Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

How to Replace in PQ without indicating Columns

Hello Everyone

 

My table has a structure like

 

A1B1C1D1
1Value1OKOK
2OKOKValue3
3tOKValue2OK

 

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!!

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

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

View solution in original post

4 REPLIES 4
wdx223_Daniel
Super User
Super User

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

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

Nathaniel_C
Super User
Super User

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

 

Nathaniel_C_0-1653613511311.png

Nathaniel_C_1-1653613586163.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors