This post is also available in:
Hits: 6255
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?
Using the GUI
You could use the GUI and replace every possible entry with e.g. =Table.ReplaceValue(Quelle,”ä”,”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…..
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.
The next three steps merge the list with the replacement table and get the correct values.
The last step tidies up the table and converts the result.
Get the results
In your original table, add a new column entering this code:
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:
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).
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]
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″}})
Hi Vitaly,
Thank you for your interesting input.
It’s a different approach to solve this scenario.
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?
Hi Vitaly
Interesting question. Could you please provide a small example?