Creating a Power Query function that can replace values for any field

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
1,262
Office Version
  1. 365
  2. 2016
Platform
  1. 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:
FieldValue To ReplaceReplacer Value
ActivityProjectRun
Project IDWrong IDRight 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?
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
1,262
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Sorry, actually need to amend this already.

there would always be another field in play, namely the identifier. As it happens the correction may not apply on all the records. That identifier may be in 2 other columns.

Identifier FieldIdentifierChange FieldValue To ReplaceReplacer Value
Project IDPRJ-88-44578-B01ActivityProjectRun
Sub Track IDSTI-01-005784XD7Project IDPRJ-88-44578-B01PRJ-88-44578-A01
 

Forum statistics

Threads
1,175,508
Messages
5,897,844
Members
434,682
Latest member
p1nkman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top