Multiple steps Power Query custom function

Hi Mic1979,

Looking at the code I can quickly identify some issues, for example:

- except for the Input_Table you aren't using any of the specified function parameters

- in the step #"Replaced Value Stuffing Box Material" you've passed a function value RULE_6 where a table is expected and in the final parameter passed a field value where a list with column name(s) is expected.

Give this a go. Copy the full script into a new blank query, replacing everything that's there.

It illustates two techniques, (1) how your RULE_6 function can be invoked passing arguments to parameters and (2) an alternative method to deal with multiple conditions.

let
    StuffingBoxMaterialConditions = Table.FromRows(
        {
            {"DN10", "Brass", "StSt 431"},
            {"DN10", "StSt 316L", "StSt 431"}
        },
        type table [DN_Size cond=text, Stuffing_Box_Material cond=text, NewValue=text]
    ),
    lookFor = List.Buffer(Table.ToRows(Table.RemoveColumns(StuffingBoxMaterialConditions, {"NewValue"}))),
    replWith = List.Buffer(StuffingBoxMaterialConditions[NewValue]),
    Iterations = List.Buffer({0 .. List.Count(lookFor) - 1}),
    RULE_6 = (Input_Table as table, Body_Material as text, DN_Size as text /*, Stuffing_Box_Material as text, FDA_MATERIAL_CERTIFICATION as text */) =>
        Table.ReplaceValue (
            Input_Table,
            each [#"Body_Material"],
            each if [#"DN_Size"]= DN_Size /* "DN10" */ and [#"Body_Material"] = Body_Material /* "Bronze" */
                then "StSt 316L"
                else [#"Body_Material"],
            Replacer.ReplaceText,
            {"Body_Material"}
        ),
    Sample = Table.FromRows(
        {
            {"DN10", "Gold", "StSt 316L"},
            {"DN10", "Bronze", "Brass"},
            {"DN10", "Zilver", "Copper"}
        }, type table [DN_Size=text, Body_Material=text, Stuffing_Box_Material=text]
    ),
    InvokedRULE_6 = RULE_6(Sample, "Bronze", "DN10"),
    Replacer = List.Accumulate(
        Iterations, 
        InvokedRULE_6 /* pass the outcome of invoking RULE_6 */, 
        (s, a) =>
            Table.ReplaceValue(
                s, 
                each [#"Stuffing_Box_Material"],
                each if ([#"DN_Size"] = lookFor{a}{0} and [#"Stuffing_Box_Material"] = lookFor{a}{1})
                    then replWith{a}
                    else [#"Stuffing_Box_Material"],
                Replacer.ReplaceText, 
                {"Stuffing_Box_Material"}
            )
        )
in
    Replacer

Building Solutions with Custom Functions in M

I hope this is helpful