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.
Hi Power BI Community!
At first the subject sounds easy: Power Query - replace value - advanced options - match entire cell - done but I have this value:
Unique HTML Values 😐 I have 1000+ Rows but I want to know in which html values have "green", "red" and "yellow" in their code.
I can filter with TEXT Filter "green", "red" and "yellow" but the replacing the entire cell is not possible and to change every single cell (again all html values are unique).
Should I copy and filter with text but how can I do this without affecting the other data. Do you have a better solution?
Solved! Go to Solution.
Hi @Anonymous ,
In Power Query you should be able to edit the default replace code.
I'm not sure I understand exactly what you want to replace the whole value with, but somethin glike this might work for you:
//Default code
= Table.ReplaceValue(#"Changed Type","abcde","edcba",Replacer.ReplaceText,{"EVENT_TYPE"})
//Change to
= Table.ReplaceValue(#"Changed Type", each [CM HTML], each if Text.Contains([CM HTML], "Green") or Text.Contains([CM HTML], "Red") then "Replace the cell with this text" else [CM HTML], Replacer.ReplaceText, {"EVENT_TYPE"})
If this doesn't work, try changing Replacer.ReplaceText to Replacer.ReplaceValue
Pete
Proud to be a Datanaut!
How's something like this? I've broken it up into seperate code peices to show the process, but you can likely combine together if needed. It's a New Colmn, not Measure (only matters if using Direct Query)
1. This will come in handy later, but we need to know which color we are working with...
2. Next we need to know the position of hte color we are working with, in case it's not always uniform inthe HTML
Proud to give back to the community!
Thank You!
Hi @Anonymous ,
In Power Query you should be able to edit the default replace code.
I'm not sure I understand exactly what you want to replace the whole value with, but somethin glike this might work for you:
//Default code
= Table.ReplaceValue(#"Changed Type","abcde","edcba",Replacer.ReplaceText,{"EVENT_TYPE"})
//Change to
= Table.ReplaceValue(#"Changed Type", each [CM HTML], each if Text.Contains([CM HTML], "Green") or Text.Contains([CM HTML], "Red") then "Replace the cell with this text" else [CM HTML], Replacer.ReplaceText, {"EVENT_TYPE"})
If this doesn't work, try changing Replacer.ReplaceText to Replacer.ReplaceValue
Pete
Proud to be a Datanaut!
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 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |