Forum Discussion

Mic1979's avatar
Mic1979
Post Partisan
2 years ago
Solved

Replacing values in two columns simultaneously under condition

Dear all,

 

Up to now I have replaced values in one column based on a condition.

Now I am trying to replace the values in two columns simulaneously vie the following function:

 

let
RULE_8 = (Input_Table as table, Product_Series_Description as text, Body_Material as text, Stuffing_Box_Material as text) =>
Table.ReplaceValue (
Input_Table,
each [#"Body_Material"] and [#"Stuffing_Box_Material"],
each if [#"Product_Series_Description"]="2 way Angle Body Valve" and
([#"Body_Material"]= "Bronze" or [#"Body_Material"] = "StSt 316L") and
([#"Stuffing_Box_Material"]= "StSt 431" or [#"Stuffing_Box_Material"] = "StSt 316L")
then "StSt 316L" and "StSt 431"
else [#"Body_Material"] and [#"Stuffing_Box_Material"],
Replacer.ReplaceText,
{"Body_Material","Stuffing_Box_Material"})

in RULE_8

 

Howeverit doesn't work.

 

Do you have any syggestions?

 

Many thanks for your really appreciated help.

Need Help
  • ahadkarimi's avatar
    ahadkarimi
    2 years ago

    Hey Mic1979 , try and let me know if there is any problem.

    let
    RULE_8 = (Input_Table as table, Product_Series_Description as text) =>
    Table.TransformColumns(
        Input_Table,
        {
            {"Body_Material", each if [Product_Series_Description] = "2 way Angle Body Valve" and
                ([Body_Material] = "Bronze" or [Body_Material] = "StSt 316L") and
                ([Stuffing_Box_Material] = "StSt 431" or [Stuffing_Box_Material] = "StSt 316L")
            then "StSt 316L" else _},
            {"Stuffing_Box_Material", each if [Product_Series_Description] = "2 way Angle Body Valve" and
                ([Body_Material] = "Bronze" or [Body_Material] = "StSt 316L") and
                ([Stuffing_Box_Material] = "StSt 431" or [Stuffing_Box_Material] = "StSt 316L")
            then "StSt 431" else _}
        }
    )
in
    RULE_8

     

7 Replies

  • ahadkarimi's avatar
    ahadkarimi
    Solution Specialist
    2 years ago

    Hey Mic1979 , try and let me know if there is any problem.

    let
    RULE_8 = (Input_Table as table, Product_Series_Description as text) =>
    Table.TransformColumns(
        Input_Table,
        {
            {"Body_Material", each if [Product_Series_Description] = "2 way Angle Body Valve" and
                ([Body_Material] = "Bronze" or [Body_Material] = "StSt 316L") and
                ([Stuffing_Box_Material] = "StSt 431" or [Stuffing_Box_Material] = "StSt 316L")
            then "StSt 316L" else _},
            {"Stuffing_Box_Material", each if [Product_Series_Description] = "2 way Angle Body Valve" and
                ([Body_Material] = "Bronze" or [Body_Material] = "StSt 316L") and
                ([Stuffing_Box_Material] = "StSt 431" or [Stuffing_Box_Material] = "StSt 316L")
            then "StSt 431" else _}
        }
    )
in
    RULE_8

     

    • Mic1979's avatar
      Mic1979
      Post Partisan
      2 years ago

      Hei ahadkarimi,

      thanks for this, but it is not working. in the columns I suppose to change I have Erros.

       

  • Ahmedx's avatar
    Ahmedx
    Super User
    2 years ago

    Can you please share your demo input and expected output!

  • Mic1979's avatar
    Mic1979
    Post Partisan
    1 year ago

    Dear all,

    I am always dealing with the topic to 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 un 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.

     

    Thanks for your help.