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.Re...
  • AlienSx's avatar
    1 year ago
    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
  • AlienSx's avatar
    AlienSx
    1 year ago

    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
  • AlienSx's avatar
    AlienSx
    1 year ago

    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