Replacing values in two columns simultaneously under condition

[Mic1979]

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.

[Mic1979]

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.

[Ahmedx]

Can you please share your demo input and expected output!

[ahadkarimi · Accepted solution]

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]

Hei ahadkarimi,

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

[ahadkarimi]

@Mic1979 can you please share a screenshot of the error?

[Mic1979]

It is not an error message, just in the columns where I should have the new values I have instead "Error"

[Mic1979]

Here the screenshot