Forum Discussion
Power BI Desktop Query, replace multiple strings in a single command line via 'Table.ReplaceValue'
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
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-29f1bb1ae322 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
3 Replies
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-29f1bb1ae322 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
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.
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/
