This post is also available in: German

Hits: 6256

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!

With Power Query we can replace each wrong value by using the build in functions. If our users produce just 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 source. If not, well, you have to correct the values on a periodic base. Cumbersome? Yes! In this blog entry, I’ll show you a way to replace wrong values at once and, even better, without opening your pbix file.

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(Quelle,”ä”,”ae”,Replacer.ReplaceText,{“Value”}).

Using_GUI
Using GUI

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…..

List of replacements
List of replacements

Function

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

Line 7 (Table.FromList) converts the string into a list as in the picture below.

Split_string
Split string

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

merge_with_correct_values
merge correct values

The last step tidies up the table and converts the result.

functions_result
result

Get the results

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

get_correct_results
get correct results

It replaces the wrong values by using the function and without the need of adding and deleting additional columns. As a result, you get this table:

correct_values
correct values

Wait – there’s more

Well, maybe now are you thinking “oh, that’s neat, I may open my pbix file periodically and add new values into the replacement table”. For sure, you can go this path. But what if you have multiple reports and 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 “Replacement” table (see next picture with an Excel table).

external_file
external_file

All you must 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, you can enter new values into the external file and deliver a report with any typos.

More articles about Power Query can be found in the Power Query Online Buch.

Happy Querying!

Dear readers, I write my articles for you. It is my ambition to provide you with qualitatively appealing content. Please take a minute and give me a review or comment below. Thank you very much.

[yasr_visitor_multiset setid=2]

4 thoughts on “Batch replacement of wrong values in Power Query

  • 8. June 2018 at 20:48
    Permalink

    I think the multiple is better implemented via List.Accumulate, here is the code and the example:

    let
    Text.ReplaceMany = (input as text, replacements as list) =>
    List.Accumulate(
    replacements,
    input,
    (corrected, pair) => Text.Replace(corrected, pair{0}, pair{1})
    )
    in
    Text.ReplaceMany(“Hello, world!”, {{“o”,”0″}, {“ll”,”11″},{“e”,”3″}})

    Reply
    • 9. June 2018 at 10:45
      Permalink

      Hi Vitaly,
      Thank you for your interesting input.
      It’s a different approach to solve this scenario.

      Reply
    • 11. July 2020 at 17:35
      Permalink

      How would you code the above in powerBI if you had a table of replacements and a table with a column of text to clean?

      Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: