Power Query - conditional replace/clear entire cell in multiple columns

Question

I'm trying to clear the entire cell if it doesn't contain a given keyword. I've managed to do this for one column:

    Table.ReplaceValue(#"PrevStep",each [#"My Column"], each if Text.PositionOf([#"My Column"],"keyword")>-1 then [#"My Column"] else null,Replacer.ReplaceValue,{"My Column"})

The problem is I need to iterate/repeat that step for a number of columns... the number of columns may vary and column names also may be different every time. I can have all those column names put into a list but I'm not able to use it.

The solution I'm looking for may look like this

for each ColNam in MyColumnsList

Table.ReplaceValue(#"PrevStep",each [#"ColNam"], each if Text.PositionOf([#"ColNam"],"keyword")>-1 then [#"ColNam"] else null,Replacer.ReplaceValue,MyColumnsList) 

next

but this is not the VBA code but Power Query M - and of course the problem is with #PrevStep as I would see it like a recursions... again... do not know how to process.

Is the path I follow correct or should it be done some other way Thanks Andrew

horseyride horseyride · Accepted Answer · 2021-06-08T19:21:14

Unpivot your columns to turn all the columns into two columns. Apply your replacement to the single value column then pivot it back into the original format

Power Query - conditional replace/clear entire cell in multiple columns

1 Answers