Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
User | Count |
---|---|
98 | |
89 | |
82 | |
70 | |
67 |
User | Count |
---|---|
114 | |
104 | |
101 | |
72 | |
65 |