GraH
Well-known Member
- Joined
- Mar 22, 2020
- Messages
- 1,575
- Office Version
- 365
- Platform
- Windows
Hi,
I'm tweaking a relative long procedure with several Power Queries in several files that in the end allows me to generate a normalized data model in Power Pivot.
When validating the reports, it happens we detect data quality issues. When we correct the data sources it might be I need to run the whole procedure again. Clearly I want to avoid doing that many times in the reporting period. All files are on SharePoint and performance in general is rather bad. I still need to investigate if and how I can move to using synchronized files as Matt Allington suggested in another thread over here.
What I'm looking for is a custom function that can handle a replace values of any field given in any table. The definition of the table and a 2 simple examples are below:
Currently I change the values manually in the latest file I generate before importing in the data model. But when I do need to run part of the code again, when people provide an updated source file e.g., I need to remember I actually changed values and need to redo this manual step. It's against company policy to use VBA, so I'm looking for a PQ solution. My head spins when I think "for ... each".
The function would take a table object as input and generate a table object as output. Then iterate over the columns and if it finds a matching field, look for the matching value to replace and replace it. It's the double iteration part that puzzles me. How would you clever wizards do this?
I'm tweaking a relative long procedure with several Power Queries in several files that in the end allows me to generate a normalized data model in Power Pivot.
When validating the reports, it happens we detect data quality issues. When we correct the data sources it might be I need to run the whole procedure again. Clearly I want to avoid doing that many times in the reporting period. All files are on SharePoint and performance in general is rather bad. I still need to investigate if and how I can move to using synchronized files as Matt Allington suggested in another thread over here.
What I'm looking for is a custom function that can handle a replace values of any field given in any table. The definition of the table and a 2 simple examples are below:
| Field | Value To Replace | Replacer Value |
| Activity | Project | Run |
| Project ID | Wrong ID | Right ID |
Currently I change the values manually in the latest file I generate before importing in the data model. But when I do need to run part of the code again, when people provide an updated source file e.g., I need to remember I actually changed values and need to redo this manual step. It's against company policy to use VBA, so I'm looking for a PQ solution. My head spins when I think "for ... each".
The function would take a table object as input and generate a table object as output. Then iterate over the columns and if it finds a matching field, look for the matching value to replace and replace it. It's the double iteration part that puzzles me. How would you clever wizards do this?
