Forum Discussion
How to Replace in PQ without indicating Columns
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!!
NewStep=Table.ReplaceValue(PreviousStepName,"Value","No",(x,y,z)=>if Text.Contains(x,y) then z else x,Table.ColumnNames(PreviousStepName))
4 Replies
- Nathaniel_C
Community Champion
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
Helper I
Nice try, but still having column names in formula which won't work on my case after.
- wdx223_Daniel
Community Champion
NewStep=Table.ReplaceValue(PreviousStepName,"Value","No",(x,y,z)=>if Text.Contains(x,y) then z else x,Table.ColumnNames(PreviousStepName))
- JOSELUISMTZRMZ1
Helper I
Yep this can work, I will use my step where I list my columnnames. Thank you