Multiple steps Power Query custom function

[Mic1979]

Dear all,

I am approaching to the Power Query Custom function.

My function is named Rule_6 with the following parameters as input:

• Input_Table as table
• Body_Material as text
• DN_Size as text
• Stuffing_Box_Material as text
• FDA_MATERIAL_CERTIFICATION as text

The target of this function is to replace a text in the column Body_Material based on a condition.

The code is the following, and it works:

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"]= "DN10" and
[#"Body_Material"] = "Bronze"
then "StSt 316L"

else [#"Body_Material"],

Replacer.ReplaceText,
{"Body_Material"})

I would like to use the output of this in another step, and I tried this:

let
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"]= "DN10" and
[#"Body_Material"] = "Bronze"
then "StSt 316L"

else [#"Body_Material"],

Replacer.ReplaceText,

{"Body_Material"}),

#"Replaced Value Stuffing Box Material" = Table.ReplaceValue(RULE_6, each [#"Stuffing_Box_Material"],
each if
[#"DN_Size"] = "DN10" and
[#"Stuffing_Box_Material"] = "Brass"
then "StSt 431"
else if
[#"DN_Size"] = "DN10" and
[#"Stuffing_Box_Material"] = "StSt 316L"
then "StSt 431"
else [#"Stuffing_Box_Material"],
Replacer.ReplaceText,
[#"Stuffing_Box_Material"])

in #"Replaced Value Stuffing Box Material"

However I have this error message:

Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

Do you have any suggestions?

Thanks for your help in advance.

[m_dekorte · Accepted solution]

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

[Mic1979]

Many thanks. Really helpful

[Mic1979]

Hello

it works, many thanks, eneve if I need to study it as it contains a lot of functions I never used.

However, How to use this in another table? I saw that the result of your code is a table.

Just to be clear, I have a master table where I wanted to use the Rule_6 posted in the starting post. Here an abstact:

/* TOTAL VOLUME Y3 */

#"TOTAL VOLUME Y3" = Table.AddColumn(#"Removed Columns25", "TOTAL VOLUME Y3", each [OEM VOLUMES Y3]+[DISTRIBUTOR VOLUMES Y3]),

#"Removed Columns26" = Table.RemoveColumns(#"TOTAL VOLUME Y3",{"Y3"}),

#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns26",{{"OEM VOLUMES Y3", Int64.Type}, {"DISTRIBUTOR VOLUMES Y3", Int64.Type}, {"TOTAL VOLUME Y3", Int64.Type}}),

#"Filtered Rows" = Table.SelectRows(#"Changed Type2", each ([TOTAL VOLUME Y3] <> 0)),

#"Reordered Columns1" = Table.ReorderColumns(#"Filtered Rows",{"Helper_1", "Helper_2", "Region", "Project_Step", "Function_Description", "Connection_Type_Description", "Product_Series_Description", "Port_Type_1_2", "Third_Port", "Ambient_Temperature", "cRUUS_UL_Listed", "ATEX_2_22_zones", "FDA_MATERIAL_CERTIFICATION", "Body_Certificate_3_1", "Seat_leakage_Class_VI", "UKCA_Certification_Ex_2_22", "EAC_CUTR", "CRN", "Analog_Input", "Digital_Output", "Digital_Communication_Protocol", "IP_Degree", "Oxygen_application_Service", "Feedback", "PCBA_Type", "DN_Size", "Body_Material", "Stuffing_Box_Material", "Disc_Seal_Material", "PTFE_Wiper_Seal", "Electrical_Connection", "UL_ClassI_Div2", "OEM VOLUMES Y3", "DISTRIBUTOR VOLUMES Y3", "TOTAL VOLUME Y3"}),

/* RULE 1 */
#"RULE_1" = RULE_1 (#"Reordered Columns1", "Product_Series_Description", "Port_Type_1_2"),

#"RULE_1_1" = RULE_1_1 (#"RULE_1", "Product_Series_Description", "Third_Port", "Connection_Type_Description"),

#"RULE_1_1_2" = RULE_1_1_2 (#"RULE_1_1","Product_Series_Description","Port_Type_1_2"),

/* RULE 2 */
#"RULE_2" = RULE_2 (#"RULE_1_1_2", "Connection_Type_Description","Third_Port"),

/* RULE 3 */
#"RULE_3" = RULE_3 (#"RULE_2", "Body_Material", "Port_Type_1_2"),

#"RULE_3_1" = RULE_3_1 (#"RULE_3", "Stuffing_Box_Material", "Port_Type_1_2")

in
#"RULE_3_1"

My target is to build some functions and call them in this master table.

Could you support me?

I really appreciate your help and I want to thank you so much.

[m_dekorte]

Hi @Mic1979,

Here's my advise on how to implement this logic into your Master query. Open the Advanced Editor and place your cursor after the let clause on line 1, press enter. Now copy this section on line 2.

    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"}
        ),

This code creates a supporting table, lists and the RULE_6 function.

If it helps visualizing, you can think of Variables (Steps or Identifiers) as a Name that stores the outcome of an expression - which can be passed along to another expression. The final Variable/Step/Identifier in your abstract is: #"RULE_3_1"  let's incorporate the remainder of the solution there, begin by entering a comma at the end of that line and removing the remainder of the code (in-clause and identifier) as illustrated here.

    #"RULE_3_1" = RULE_3_1 (#"RULE_3", "Stuffing_Box_Material", "Port_Type_1_2"),
    InvokedRULE_6 = RULE_6(#"RULE_3_1", "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

I hope this is helpful.