Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Replacing values in multiple columns based on condition in Power Query

Hello,   I have a table like this: Date     plan         unplan         internal         external         production          day flag     1/1/2020     0 0 50 70 6000 Production ...
Need Help
  • ziying35's avatar
    ziying35
    6 years ago

    Hi, Anonymous 

    Based on the simulated data source and the data processing logic you provided, write the query code like this:

    = Table.ReplaceValue(Source, each [day flag]="Down Day", null, (x,y,z)=> if y then z else x, List.Range(Table.ColumnNames(Source),1,4))

    If my code solves your problem, mark it as a solution

  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, Anonymous 

     

    As what is suggested by ziying35 , I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may insert a new step as below. On your side, you may add all corresponding columns in {"plan","unplan","internal","external",...}.

    = Table.ReplaceValue(Source, each [day flag]="Down Day", null, (x,y,z)=> if y then z else x, List.Select(Table.ColumnNames(#"Changed Type"),
each  List.Contains( {"plan","unplan","internal","external"},_)
)
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ziying35's avatar
ziying35
Impactful Individual
6 years ago

Hi, Anonymous 

Based on the simulated data source and the data processing logic you provided, write the query code like this:

= Table.ReplaceValue(Source, each [day flag]="Down Day", null, (x,y,z)=> if y then z else x, List.Range(Table.ColumnNames(Source),1,4))

If my code solves your problem, mark it as a solution

  • Rickmaurinus's avatar
    Rickmaurinus
    Helper V
    4 years ago

    Hey ziying35.

     

    I included your solution in my blogpost at https://gorilla.bi/power-query/replace-values/. 

    However, I'm accustomed to working with Replacer.ReplaceValues and Replacer.ReplaceText. I don't fully understand your example. 

    Can you elaborate on the inner workings of : 

    (x,y,z)=> if y then z else x?

     

    Warm regards,

    Rick

    • ziying35's avatar
      ziying35
      Impactful Individual
      4 years ago

      Simple understanding: the second parameter of the function Table.ReplaceValue is represented by y, the third parameter is represented by z, and the replaced element itself is represented by x

      • Rickmaurinus's avatar
        Rickmaurinus
        Helper V
        4 years ago

        Hi ziying,

         

        Thanks for adding the clarification. I could imagine in my head that happens. I'm just stumbled by how you thought of doing this, please help me understand. 

         

        No where does it say that Y represents the Table function, or that the third parameters should be the Z or what X should do. 

         

        If I can ask

        1. How did you come up with this in the first place? Is there any documentation on this? 

        2. In theory, you could have replaced the function letters with any other letter, correct? 

         

        Best,

        Rick

    • lbendlin's avatar
      lbendlin
      Super User
      4 years ago

      x = original value

      y = match condition  (true or false)

      z = replacement value

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi ziying35 , Sorry for pinging you on this old topic. This method works perfectly if I just want to replace the value with a fixed value (null @ 1 @2, etc). But what if I want to multiple the old value with 1000? For example if the old value is 1000, instead of changing it to null i want to make it 1,000,000.

    I tried changing the null to below, but it didn't work.

    each _ * 1000

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Figured it out minutes after asking this.

       

      = Table.ReplaceValue(
   Source, 
   each [day flag]="Down Day", 
   each _, // this step become redundant, but needed to fill the syntax requirement
   // (x,y,z)=> if y then z else x, --- this is the original one, below is edited.
   (x,y,z)=> if y then x*1000 else x,  // we just ignore z, and replace it with x*1000
   List.Range(Table.ColumnNames(Source),1,4)
)
    • dufoq3's avatar
      dufoq3
      Community Champion
      2 years ago

      Hi Anonymous, there are always many ways. Here you can find record base solution. I'm refering to 1st post sample data:

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJQ0lFSAAIQDcKmIMIcRJgZGICogKL8lNLkksz8PAWXxEqlWB2QViNsWpGxX36eLladxth0GoJtMgKThsZgyiW/HFmbCTZtxiDCAuJq3G41xWojWMTQFGo7FhvNyPaiOTk6YwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"    plan    " = _t, #"    unplan    " = _t, #"    internal    " = _t, #"    external    " = _t, #"    production     " = _t, #"    day flag    " = _t]),
    ColumnsTrim = Table.TransformColumnNames(Source, Text.Trim),
    ChangedType = Table.TransformColumnTypes(ColumnsTrim,{{"Date", type date}, {"plan", Int64.Type}, {"unplan", Int64.Type}, {"internal", Int64.Type}, {"external", Int64.Type}, {"production", Int64.Type}, {"day flag", type text}}),
    EnterColumnsToReplace = "plan, unplan, internal, external",
    ColsList = List.Transform(Text.Split(EnterColumnsToReplace, ","), Text.Trim),
    Ad_Replaced = Table.AddColumn(ChangedType, "Replaced", each 
        if [day flag] = "Down Day" then Record.Combine({_, Record.TransformFields(Record.SelectFields(_, ColsList), List.Transform(ColsList, (y)=>  {y, (x)=> x * 1000}))}) else _,
  type record ),
    Replaced = Table.FromRecords(Ad_Replaced[Replaced], Value.Type(ChangedType))
in
    Replaced