Power BI Desktop Query, replace multiple strings in a single command line via 'Table.ReplaceValue'

[Andreas_H]

Good Evening Power BI community,

after a litlle pause I am now back into Power BI Desktop and I am making good progress given the fact that I am sill to be determined as beginner...

I figured hout how to rename multiple columns in one command line:

= Table.RenameColumns(#"Spalte nach Trennzeichen teilen", {{"Act @B17", "PA Act @B17"}, {"Bud (p) @B17", "FY Bud (p) @B17"}})

Here I am renaming "Act @B17" and "Bud (p) @B17".

Tonight I wanted to apply the same logic to Value Replacement via 'Table.ReplaceValue'

So a single Replacement works:

= Table.ReplaceValue(#"Geänderter Typ","Innovation Budget","IB",Replacer.ReplaceValue,{"Portfolio Category"})

So I wanted to add a second replacement pattern:

= Table.ReplaceValue(#"Geänderter Typ", {"Innovation Budget","IB", Replacer.ReplaceValue,{"Portfolio Category"}}, {"Late Development","LD", Replacer.ReplaceValue,{"Portfolio Category"}})

But it does not work. Is it possible at all to do this? Where do I have to put the braces? Do I need to repeat the name of the column in which to replace '{"Portfolio Category"}' and the replacement pattern 'Replacer.ReplaceValue' for every string I want to replace?

Tonight I am quite despreate and my search on the internet did reveal more complex options I can not deal with at the moment. Since in the renaming it works so nice, why not in the replacement as well?!

I sincerely hope that someone from the community does know the trick...

Best regards from Berlin, Andreas

[ImkeF · Accepted solution]

Hi Andreas,

this will not work unfortunately.

The function-definition will show you why:

Table.RenameColumns(table as table, renames as list, optional missingField as nullable number) as table 

in this function the rename-arguments are expected as list & this is normally a sign that multipe values are accepted. You can also indicate this at the syntax of your actual formula:

= Table.RenameColumns(#"Spalte nach Trennzeichen teilen", { {"Act @B17", "PA Act @B17"}, {"Bud (p) @B17", "FY Bud (p) @B17" } } )

You have your renamings in curly brackets and then there is another curly bracket around them all: This makes them a list. So you can add as many into the list as needed.

But Table.ReplaceValue(table as table, oldValue as any, newValue as any,replacer as function, columnsToSearch as {Text}) 

is missing the list-element

and the syntax is missing the curly brackets:

Table.ReplaceValue(#"Geänderter Typ","Innovation Budget","IB",Replacer.ReplaceValue,{"Portfolio Category"})

So no chance with this command unfortunately.

BTW: I've set up a user group in Berlin: https://www.pbiusergroup.com/communities/community-home?CommunityKey=55090a51-8ae4-4165-abd6-29f1bb1... and we try to have our first meeting end of September (still organizing a meeting location). Would be great to see you there!

Cheers - Imke

[Andreas_H]

Dear Imke,

thank you so much for your quick and comprehensive reply! Getting the function definition explained this well helps me to evaluate future function usage much better!

Since I have the definitive confirmation my desired approach does not work I can focus on alternatives. This is good progress!

Thank you once again & best regards from Berlin, Andreas

PS: I joined and subscribed the Berlin User Group.

[ImkeF]

You're welcome!

Multiple replacements is not trivial, as depending on your method, a once replaced value can be overwritten by a following replacement. This article describes the risks: http://www.thebiccountant.com/2016/05/22/multiple-replacements-in-power-bi-and-power-query/