Forum Discussion
Replace value entire cell
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?
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
2 Replies
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
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...
Color Found = IF ( SEARCH("green", 'Color Shift'[HTML Data],,0) <> 0,"green",IF ( SEARCH("yellow", 'Color Shift'[HTML Data],,0) <> 0,"yellow",IF ( SEARCH("black", 'Color Shift'[HTML Data],,0) <> 0,"black")))2. Next we need to know the position of hte color we are working with, in case it's not always uniform inthe HTML
Color Position = IFERROR(SEARCH("green",'Color Shift'[HTML Data]),IFERROR(SEARCH("black",'Color Shift'[HTML Data]),IFERROR(SEARCH("yellow",'Color Shift'[HTML Data]),0)))3. Now, take everything from the LEFT of the found color position & whatever new text you need (this is assuming you are changing everything to one uniform color - add IF/Then if needing multiple colors based on original color) & everything from the RIGHT of the found color position. (Some funky math looking at total length - position found - length of color name + 1 back in because 0's exist in the world)New HTML = LEFT('Color Shift'[HTML Data], 'Color Shift'[Color Position] - 1) & "New Color" & RIGHT('Color Shift'[HTML Data], LEN('Color Shift'[HTML Data]) - 'Color Shift'[Color Position] - LEN('Color Shift'[Color Found]) + 1)
