Forum Discussion

Mic1979's avatar
Mic1979
Post Partisan
1 year ago
Solved

Replacing values with Iterations

Dear all,

I am always dealing with the topic to replace values and solve an issue I have in my daily job.

I found a very useful blog, and I successfully used this function:

 

NEW_TABLE = Table.ReplaceValue (
RULE_1,
each [Body_Material],
each List.Accumulate(
Table.ToRecords(ReplacementTable),
[Body_Material],
(valueToReplace, replaceOldNewRecord) =>
Text.Replace (
valueToReplace,
replaceOldNewRecord[Old_Material],
replaceOldNewRecord[New_Material]
)
),
Replacer.ReplaceText,
{"Body_Material"}
)

 

In this case, I am replacing the values in one column. How to do this is two columns at the same step?

 

I could apply the same adding a step and changing the column where tu apply the function, however I think this will be more time consuming operation that have everything in the same step.

  • let
        RULE_1 = #table({"Column1", "Column2"}, {{"a b c", "c b a"}, {"c a b", "a c b"}}),
        ReplacementTable = #table({"old", "new"}, {{"a", "1"}, {"b", "2"}, {"c", "3"}}), 
        replacements = List.Buffer(Table.ToRecords(ReplacementTable)),
        using_table_replace_value = Table.ReplaceValue(
            RULE_1, 
            null,
            null,
            (v, o, n) => List.Accumulate(replacements, v, (s, c) => Text.Replace(s, c[old], c[new])),
            {"Column1", "Column2"}
        )
    in
        using_table_replace_value
  • Many things are wrong in your code starting with "each..." in 4th argument of Table.ReplaceValue. I would recommend you to read something about Table.ReplaceValue (this article by Rick de Groot is pretty good). 

    Table.ReplaceValue is flexible but not the easiest one to understand (and not very performant at the same time) if you want to use it's full potential. 

    Apparently your goal is not to investigate Table.ReplaceValue but solve your particular problem. So why don't you simply show your data, explain your problem? Then maybe Table.TransformColumns would become  a better choice.

    I am trying to fix your code but can't do much w/o sample of your data and problem description. Here I am giving up, sorry. 

    (
        Input_Table as table,
        ReplacementTable as table,
        InputColumnToChange1 as text, //Port type 1-2
        InputColumnToChange2 as text, // Body Material
        InputColumnToChange3 as text // Stuffing Box Material
    ) =>
    let
        Replacements = List.Buffer(Table.ToRecords(ReplacementTable)),
        NewTable = Table.ReplaceValue(
            Input_Table, null, (x) => Record.Field(x, InputColumnToChange1) <> "Butt Welding ASME BPE",
            (v, o, n) => if n then List.Accumulate(Replacements, v, (s, c) => Text.Replace(s, c[Old_Material], c[New_Material])) else v,
            {InputColumnToChange2, InputColumnToChange3}
        )
    in
        NewTable
  • okay, i see that now. So... using List.Accumulate to go over replacements table was bad idea. I'd prefer to create a record from replacements table and check it's fields using Record.FieldOrDefault to find your values and replace them. It's faster. Anyway, below are 3 options to achieve the same result. 

    1. Table.ReplaceValue

    (data, replacements, condition_column, condition_value, columns_list as list) => 
        [
            // generates a record with replacements
            repl = Function.Invoke(Record.FromList, List.Reverse(Table.ToColumns(replacements))),
            // replace values in list of columns
            result = Table.ReplaceValue(
                data, 
                (x) => Record.Field(x, condition_column) <> condition_value, // "old"
                null, // "new"
                (value, old, new) => if old then Record.FieldOrDefault(repl, value, value) else value, 
                columns_list
            )
        ][result]

    2. Table.TransformRows

    (data, replacements, condition_column, condition_value, columns_list as list) => 
        [
            // generates a record with replacements
            repl = Function.Invoke(Record.FromList, List.Reverse(Table.ToColumns(replacements))),
            transformations = List.Transform(columns_list, (name) => {name, (x) => Record.FieldOrDefault(repl, x, x)}),
            // replace values in rows
            replace = Table.TransformRows(
                data, (x) => if Record.Field(x, condition_column) = condition_value 
                    then Record.TransformFields(x, transformations)
                    else x
            ), 
            result = Table.FromRecords(replace)
        ][result]

    3. Table.TransformColumns

    (data, replacements, condition_column, condition_value, columns_list as list) => 
        [
            // generates a record with replacements
            repl = Function.Invoke(Record.FromList, List.Reverse(Table.ToColumns(replacements))),
            transformations = List.Transform(columns_list, (name) => {name, (x) => Record.FieldOrDefault(repl, x, x)}),
            // replace values in list of columns
            result = Table.SelectRows(data, (x) => Record.Field(x, condition_column) = condition_value) & 
                Table.TransformColumns(
                    Table.SelectRows(data, (x) => Record.Field(x, condition_column) <> condition_value), 
                    transformations
                )
        ][result]

     and file

32 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Mic1979,

     

    Table.ReplaceValue processes one value at a time for a specific column and does not support replacing values across multiple columns simultaneously using each Column1, Column2.

     

    To handle replacements in multiple columns, use Table.TransformColumns. This function allows you to apply transformations to multiple columns within a single function call, enabling individual replacement logic for each column.

     

    Did I answer your question? Mark my post as a solution, this will help others!
    If my response(s) assisted you in any way, don't forget to give "Kudos"

     

    Regards,

    Vinay Pabbu

    • Mic1979's avatar
      Mic1979
      Post Partisan

      This is interesting, however I would appreciate if you could provide some code example to implement the function in my case, base on what I posted before. I am not really familiar with Power Query M-language.

       

      Thanks.

      • Anonymous's avatar
        Anonymous
        Not applicable

        Hi Mic1979,

         

        Based on my R&D, here is the query from your logic.


        NEW_TABLE = Table.TransformColumns(
        RULE_1,
        {
        {"Body_Material", each List.Accumulate(
        Table.ToRecords(ReplacementTable),
        _, // Current value for each row in Body_Material
        (valueToReplace, replaceOldNewRecord) =>
        Text.Replace(
        valueToReplace,
        replaceOldNewRecord[Old_Material], // Old value to replace
        replaceOldNewRecord[New_Material] // New value to use
        )
        )},
        {"Other_Column", each List.Accumulate(
        Table.ToRecords(ReplacementTable),
        _, // Current value for each row in Other_Column
        (valueToReplace, replaceOldNewRecord) =>
        Text.Replace(
        valueToReplace,
        replaceOldNewRecord[Old_Material], // Old value to replace
        replaceOldNewRecord[New_Material] // New value to use
        )
        )}
        }
        )

         

        Did I answer your question? Mark my post as a solution, this will help others!
        If my response(s) assisted you in any way, don't forget to give "Kudos"


        Regards,
        Vinay Pabbu

  • let
        RULE_1 = #table({"Column1", "Column2"}, {{"a b c", "c b a"}, {"c a b", "a c b"}}),
        ReplacementTable = #table({"old", "new"}, {{"a", "1"}, {"b", "2"}, {"c", "3"}}), 
        replacements = List.Buffer(Table.ToRecords(ReplacementTable)),
        using_table_replace_value = Table.ReplaceValue(
            RULE_1, 
            null,
            null,
            (v, o, n) => List.Accumulate(replacements, v, (s, c) => Text.Replace(s, c[old], c[new])),
            {"Column1", "Column2"}
        )
    in
        using_table_replace_value
    • Mic1979's avatar
      Mic1979
      Post Partisan

      It works!!

      Thanks a lot for your help!!

    • Mic1979's avatar
      Mic1979
      Post Partisan

      Hello

       

      I was trying to add a condition now:

       

      (
      Input_Table as table,
      ReplacementTable as table,
      InputColumnToChange1 as text, //Port type 1-2
      InputColumnToChange2 as text, // Body Material
      InputColumnToChange3 as text // Stuffing Box Material
      ) =>

      let

      Replacements = List.Buffer(Table.ToRecords(ReplacementTable)),
      NewTable = Table.ReplaceValue(
      Input_Table,
      null,
      null,
      each if [InputColumnToChange1] <> "Butt Welding ASME BPE" then (v,n,o) => List.Accumulate (
      Replacements,
      v, (s,c) => Text.Replace (
      s, c[Old_Material], c[New_Material]))
      else _,
      {"Body_Material", "Stuffing_Box_Material"}
      )

      in
      NewTable

       

      but this is not working.

      Did I put the "if" in the wrong place?

       

      Thanks again.

      • AlienSx's avatar
        AlienSx
        Super User

        Many things are wrong in your code starting with "each..." in 4th argument of Table.ReplaceValue. I would recommend you to read something about Table.ReplaceValue (this article by Rick de Groot is pretty good). 

        Table.ReplaceValue is flexible but not the easiest one to understand (and not very performant at the same time) if you want to use it's full potential. 

        Apparently your goal is not to investigate Table.ReplaceValue but solve your particular problem. So why don't you simply show your data, explain your problem? Then maybe Table.TransformColumns would become  a better choice.

        I am trying to fix your code but can't do much w/o sample of your data and problem description. Here I am giving up, sorry. 

        (
            Input_Table as table,
            ReplacementTable as table,
            InputColumnToChange1 as text, //Port type 1-2
            InputColumnToChange2 as text, // Body Material
            InputColumnToChange3 as text // Stuffing Box Material
        ) =>
        let
            Replacements = List.Buffer(Table.ToRecords(ReplacementTable)),
            NewTable = Table.ReplaceValue(
                Input_Table, null, (x) => Record.Field(x, InputColumnToChange1) <> "Butt Welding ASME BPE",
                (v, o, n) => if n then List.Accumulate(Replacements, v, (s, c) => Text.Replace(s, c[Old_Material], c[New_Material])) else v,
                {InputColumnToChange2, InputColumnToChange3}
            )
        in
            NewTable
  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion

    Mic1979 Try this: 

    NEW_TABLE
      = Table.ReplaceValue(
        RULE_1, 
        each [Body_Material], 
        each List.Accumulate(
          Table.ToRecords(ReplacementTable), 
          [Body_Material], 
          (valueToReplace, replaceOldNewRecord) =>
            Text.Replace(
              valueToReplace, 
              replaceOldNewRecord[Old_Material], 
              replaceOldNewRecord[New_Material]
            )
        ), 
        Replacer.ReplaceText, 
        {"Body_Material", "Some other Column"}
      )
    • Mic1979's avatar
      Mic1979
      Post Partisan

      Thanks for the feedback.

       

      I tried but the second column is not changed.

       

      I was thinking of something similar to this:

       

      NEW_TABLE = Table.ReplaceValue(
                                   RULE_1,
                                   each {[Body_Material],[Some other Column]}
                                   each List.Accumulate(
                                                               Table.ToRecords(ReplacementTable),
                                                               {[Body_Material],[Some other Column]},
                                                               (valueToReplace, replaceOldNewRecord) =>
                                                                           Text.Replace(
                                                                                      valueToReplace,
                                                                                      replaceOldNewRecord[Old_Material],
                                                                                      replaceOldNewRecord[New_Material]
                                                                            )
                                                                ),
                                 Replacer.ReplaceText,
                                 {"Body_Material", "Some other Column"}
                                  )

       

      but in this case neither the first column nor the second are changed.

       

  • Anonymous's avatar
    Anonymous
    Not applicable

    Try

     

    List.ReplaceValue(List.Skip(Record.ToList(_)), "OldValue", "NewValue", Replacer.ReplaceText)

     

    --Nate

    • Mic1979's avatar
      Mic1979
      Post Partisan

      Thanks for your feedback.

       

      However, i did not understand how to add your code in mine.

       

      Thanks.

  • Mic1979's avatar
    Mic1979
    Post Partisan

    Thanks for your input. I will try both solutions in the next hours.

    Really Appreciated.

  • Anonymous's avatar
    Anonymous
    Not applicable

    I don't know why you all have to get all fancy, when plain old Table.Join to your replacement table would work just fine. Even two separate join steps would probably be more efficient if you need to replace values in two columns.

     

    You know, you could just do

     

    = Table.AddColumn(PriorStepOrTableName, "Replaced", each List.ReplaceMatchingItems(List.Skip(Record.ToList(_)), List.Zip({ReplacementTable[Column1], ReplacementTable[Column2]})))

     

    Then just remove your old columns, expand the list values to columns, and that's it.

     

    --Nate