Batch replacement of wrong values in Power Query

Hits: 290

Do you live in an ideal world, where data is always clean and correct? Or does your company have a Data Warehouse with properly cleaned data? Well, then you may skip this blog entry. Otherwise, read on! You will learn how to do a batch replacement of wrong values in Power Query and, even better, without opening your pbix file(s).

With Power Query, we can replace a wrong value by using the built-in functions. If your users would produce only one or two wrong entries, this is absolutely the way to go. But, users produce wrong entries again and again. Lucky you, if you have a proper DWH as a source. If not, well, you will have to correct the values on a periodic base. Cumbersome? Yes!

Setting the stage

Working in a multinational company, it is evident, that users don’t use local chars. In German, we use umlaut marks as Ä, Ö and Ü. This umlaut marks must be translated in “AE”, “OE” and “UE”. (see table below). How to do that?

entries with umlauts
entries with umlauts

Using the GUI

You could use the GUI and replace every possible entry with e.g. =Table.ReplaceValue(Source,”ä”,”ae”,Replacer.ReplaceText,{“Value”}).

No big deal for three letters. But if you want to replace e.g. 30 letters and don’t have the possibility to correct them in the source? Well, using 30 times the GUI would be a little bit boring…..

Batch replacement

A better way to deal with multiple replacements could be correcting the values in batch. All you need is a table and a function.

Table “Replacements”:

List of replacements
List of replacements

Function fn_Replace_Chars:

The function is simple. You need to merge the original table with the table “Replacements” and swap the chars in questions with the correct ones.

The function takes a text value as input and splits the string by char.

Split_string
Split string

The next three steps will merge the list with the replacements table and get the correct values.

merge_with_correct_values
merge correct values

The last steps tidy up the table and convert the result.

functions_result
result

Get the results

In your original table, add a new column entering this code:

The code replaces the wrong values by using the function “fn_Replace_Chars” and without the need of adding and deleting additional columns. As a result, you’ll get this table:

correct_values
correct values

The umlauts marks are replaced with the right chars!

Wait – there’s more

Well, maybe now you are thinking “oh, that’s neat, I may open my pbix file periodically and add new values into the replacements table”. For sure, you can go this path. But, what if you have multiple reports and you must replace the same wrong values again and again? I bet, you don’t want to open all of them…

Create an external file

Create an external file (txt, csv or Excel). Add the same values as in the “Replacements” table (see next picture with an Excel table).

external_file
external_file

All you have to do now is to import the table into your pbix file and replace the “Replacements” table with the imported table. Rename the new table to “Replacements”. That’s all.

This way, each time you discover a wrong value, you can enter new values into the external file and deliver all reports without any typos.

Do you want to learn more about Power BI or Power Query?
Take a look here.

Happy Querying!