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 = (I...
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

     

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.