Forum Discussion

JOSELUISMTZRMZ1's avatar
4 years ago
Solved

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

  • 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's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity 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's avatar
      JOSELUISMTZRMZ1
      Icon for Helper I rankHelper I

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

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion

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

    • JOSELUISMTZRMZ1's avatar
      JOSELUISMTZRMZ1
      Icon for Helper I rankHelper I

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