Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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/
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
82 | |
70 | |
63 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |