How to Replace in PQ without indicating Columns

[JOSELUISMTZRMZ1]

Hello Everyone

My table has a structure like

A1	B1	C1	D1
1	Value1	OK	OK
2	OK	OK	Value3
3t	OK	Value2	OK

The thing is that the name columns cames from a Transpose step which will make they have a "dynamic" name.

What I need is to replace all cells contain "Value" to "NO". Since no wildcard options and I can't replace with Startwith because I'd need the column name which will change every update I'm stuck how to do.

I found this

#"Core Columns" = {"ColumnX"},

#"Dynamic Columns" = List.Difference(Table.ColumnNames(#"MyTable"),#"Core Columns"),

=Table.ReplaceValue(#"MyTable","Value","NO",Replacer.ReplaceValue, #"Dynamic Columns2")

Which works for status "Value" but doesn't work for wildcard  "Value'%'" or StartsWith("Column").

Any suggestion how to replace values that "StartsWith" a certain text to a specific Value in the whole table without using the column names?

Thanks!!

[wdx223_Daniel · Accepted solution]

NewStep=Table.ReplaceValue(PreviousStepName,"Value","No",(x,y,z)=>if Text.Contains(x,y) then z else x,Table.ColumnNames(PreviousStepName))

[JOSELUISMTZRMZ1]

Yep this can work, I will use my step where I list my columnnames. Thank you

[Nathaniel_C]

Hi @JOSELUISMTZRMZ1 ,

Please try this.

Replace Value with NO then in all the columns Extract first 2 characters.  

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[JOSELUISMTZRMZ1]

Nice try, but still having column names in formula which won't work on my case after.