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