null input parameters custom function

[Mic1979]

Dear all,

I have the following custom function:

(
Input_Table as table,
Adder_Table as table, 

Input_Table_Column1 as text, Input_Table_Column2 as text, Input_Table_Column3 as text, Input_Table_Column4 as text, Input_Table_Column5 as text,

Adder_Table_Column1 as text, Adder_Table_Column2 as text, Adder_Table_Column3 as text, Adder_Table_Column4 as text, Adder_Table_Column5 as text,

Adder as text
) =>

let

Input_Table_Helper_Column = Table.AddColumn (Input_Table, "HelperLPInput", each Text.Combine({
Record.Field(_, Input_Table_Column1),
Record.Field(_, Input_Table_Column2),
Record.Field(_, Input_Table_Column3),
Record.Field(_, Input_Table_Column4),
Record.Field(_, Input_Table_Column5)}, " "), type text),

Adder_Table_Helper_Column = Table.AddColumn (Adder_Table, "HelperLPAdder", each Text.Combine({
Record.Field(_, Adder_Table_Column1),
Record.Field(_, Adder_Table_Column2),
Record.Field(_, Adder_Table_Column3),
Record.Field(_, Adder_Table_Column4),
Record.Field(_, Adder_Table_Column5)}, " "), type text),

// Merge and Expand
Merged_Table = Table.NestedJoin (
Input_Table_Helper_Column,
{"HelperLPInput"},
Adder_Table_Helper_Column,
{"HelperLPAdder"},
"Adder",
JoinKind.LeftOuter
),

Input_Table_Headers = Table.ColumnNames(Input_Table),
Adder_Table_Headers = Table.ColumnNames(Adder_Table),
List_Header_Difference = List.Difference(Adder_Table_Headers,Input_Table_Headers),

Expanded_Merged_Table = Table.ExpandTableColumn (
Merged_Table,
Adder,
List_Header_Difference,
List_Header_Difference),

Remove_HelperLPInput = Table.RemoveColumns (Expanded_Merged_Table, "HelperLPInput")

in Remove_HelperLPInput

Invoked in this way:

ADDER10 = MergeLP_2 (

ADDER9, 

Body_Material_List_Price_Adder, 

"Region", "Project_Step", "Body_Material", "Stuffing_Box_Material", "DN_Size", 

"Region", "Project_Step", "Body_Material", "Stuffing_Box_Material", "DN_Size", 

"Adder")

I have some cases where input parameters can be null. I tried to put null in this way:

ADDER10 = MergeLP_2 (
ADDER9,
Body_Material_List_Price_Adder,
"Region", "Project_Step", "Body_Material", "Stuffing_Box_Material", null,
"Region", "Project_Step", "Body_Material", "Stuffing_Box_Material", null,
"Adder")

but it does not work.

Could you give me some hints?

Thanks.

[Anonymous]

Hi @Mic1979 
As we didnot hear any thing from the last response we are closing this thread.
Feel free to reach us if you need fruther assistance in future.
Thank you.

[Anonymous · Accepted solution]

Hi @Mic1979 
could you please provide sample of your tables?

[Mic1979]

Hello

I did not solve the issue..

[Anonymous]

Hi @Mic1979 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[ZhangKun]

If you want to pass null to Input_Table_Column4, you should modify it with the optional keyword, such as (optional a)=>...

Also, Record.Field(_, Input_Table_Column4) should be replaced with Record.Field(_, Input_Table_Column4 ?? default_value_if_null).

[dufoq3]

Hi @Mic1979, as @ZhangKun mentioned: you should use optional keyword, but keep in mind that all optional parameter have to be at the end!. BTW. indstead of Record.Field(_, Input_Table_Column4 ?? default_value_if_null) you can use Record.FieldOrDefault(_, Input_Table_Column4, default_value_if_null)

[Mic1979]

Hello @dufoq3 

Thanks, but why when I invoke the function in this way:

let
Source = MergeLP_3(Total_Sales_DOLLARS_TEST, PCBA_Type_List_Price_Adder, "Region", "Project_Step", "Fail_Safe_Description", null, null, null, "Region", "Project_Step", "Fail_Safe_Description", null, null, null, "Adder")
in
Source

I get this:

[dufoq3]

Because you haven't used optional keyword i.e.:

(tbl1 as table, tbl2 as table, optional col1 as text, optional col2 as text)=>

And again:

1. optional parameters have to be defined last.
2. my recommendation: do not use custom functions. You do not need them in 99% of tasks.

[Mic1979]

Hello @dufoq3 

thanks for your recommendation, I will use it.

However, I still have problems with this code.

I am writing down the final code I have and how I am invoking it:

(
Input_Table as table,
Adder_Table as table,

Input_Table_Column1 as text,
Input_Table_Column2 as text,
Input_Table_Column3 as text,
Adder_Table_Column1 as text,
Adder_Table_Column2 as text,
Adder_Table_Column3 as text,
Adder as text,

optional Input_Table_Column4 as text,
optional Input_Table_Column5 as text,
optional Adder_Table_Column4 as text,
optional Adder_Table_Column5 as text
) =>

let
Input_Table_Helper_Column = Table.AddColumn(Input_Table, "HelperLPInput", each
Text.Combine(List.Select({
Record.FieldOrDefault(_, Input_Table_Column1,""),
Record.FieldOrDefault(_, Input_Table_Column2,""),
Record.FieldOrDefault(_, Input_Table_Column3,""),
Record.FieldOrDefault(_, Input_Table_Column4,""),
Record.FieldOrDefault(_, Input_Table_Column5,"")
}, each _ <> null), " "), type text),

Adder_Table_Helper_Column = Table.AddColumn(Adder_Table, "HelperLPAdder", each
Text.Combine(List.Select({
Record.FieldOrDefault(_, Adder_Table_Column1,""),
Record.FieldOrDefault(_, Adder_Table_Column2,""),
Record.FieldOrDefault(_, Adder_Table_Column3,""),
Record.FieldOrDefault(_, Adder_Table_Column4,""),
Record.FieldOrDefault(_, Adder_Table_Column5,"")
}, each _ <> null), " "), type text),

// Merge and Expand
Merged_Table = Table.NestedJoin(
Input_Table_Helper_Column,
{"HelperLPInput"},
Adder_Table_Helper_Column,
{"HelperLPAdder"},
"Adder",
JoinKind.LeftOuter
),

Input_Table_Headers = Table.ColumnNames(Input_Table),
Adder_Table_Headers = Table.ColumnNames(Adder_Table),
List_Header_Difference = List.Difference(Adder_Table_Headers, Input_Table_Headers),

Expanded_Merged_Table = Table.ExpandTableColumn(
Merged_Table,
Adder,
List_Header_Difference,
List_Header_Difference
),

Remove_HelperLPInput = Table.RemoveColumns(Expanded_Merged_Table, "HelperLPInput")

in
Remove_HelperLPInput

Invoking

let
Source = MergeLP_3_Test(Total_Sales_DOLLARS_TEST, PCBA_Type_List_Price_Adder, "Region", "Project_Step", "Fail_Safe_Description", "Region", "Project_Step", "Fail_Safe_Description", "Adder")
in
Source

Thanks for your help.

[dufoq3]

Hi Mic, I'm sorry but I won't help you with this. You should use advantage of Power Query which is separating every single step. This would help you to debug your code and find parts with issu immediately. The way how you use power query won't help you to understand how it works because you will struggle with messy code again and again.

[Mic1979]

Ok no problem, I understand.

Thanks.

[DataNinja777]

Hi @Mic1979 ,

Here is the complete Power Query M formula with the necessary modifications to handle null values properly in one code snippet:

(
    Input_Table as table,
    Adder_Table as table, 

    Input_Table_Column1 as text, Input_Table_Column2 as text, Input_Table_Column3 as text, Input_Table_Column4 as text, Input_Table_Column5 as text,

    Adder_Table_Column1 as text, Adder_Table_Column2 as text, Adder_Table_Column3 as text, Adder_Table_Column4 as text, Adder_Table_Column5 as text,

    Adder as text
) =>

let
    Input_Table_Helper_Column = Table.AddColumn(Input_Table, "HelperLPInput", each 
        Text.Combine(List.Select({
            Record.Field(_, Input_Table_Column1)?, 
            Record.Field(_, Input_Table_Column2)?, 
            Record.Field(_, Input_Table_Column3)?, 
            Record.Field(_, Input_Table_Column4)?, 
            Record.Field(_, Input_Table_Column5)?
        }, each _ <> null), " "), type text),

    Adder_Table_Helper_Column = Table.AddColumn(Adder_Table, "HelperLPAdder", each 
        Text.Combine(List.Select({
            Record.Field(_, Adder_Table_Column1)?, 
            Record.Field(_, Adder_Table_Column2)?, 
            Record.Field(_, Adder_Table_Column3)?, 
            Record.Field(_, Adder_Table_Column4)?, 
            Record.Field(_, Adder_Table_Column5)?
        }, each _ <> null), " "), type text),

    // Merge and Expand
    Merged_Table = Table.NestedJoin(
        Input_Table_Helper_Column,
        {"HelperLPInput"},
        Adder_Table_Helper_Column,
        {"HelperLPAdder"},
        "Adder",
        JoinKind.LeftOuter
    ),

    Input_Table_Headers = Table.ColumnNames(Input_Table),
    Adder_Table_Headers = Table.ColumnNames(Adder_Table),
    List_Header_Difference = List.Difference(Adder_Table_Headers, Input_Table_Headers),

    Expanded_Merged_Table = Table.ExpandTableColumn(
        Merged_Table,
        Adder,
        List_Header_Difference,
        List_Header_Difference
    ),

    Remove_HelperLPInput = Table.RemoveColumns(Expanded_Merged_Table, "HelperLPInput")

in 
    Remove_HelperLPInput

Key Fixes:
Handles null values properly using List.Select(..., each _ <> null), so Text.Combine doesn’t break.
Uses Record.Field(_, ColumnName)?, so missing fields don’t throw errors.
Removes null fields before concatenation, ensuring clean text output.
Now, your function should work perfectly even when passing null as an input parameter.  Let me know if you need any tweaks!

Best regards,

[Mic1979]

Hello @DataNinja777 ,

Thanks for your help.

I had an error in the highlighted part:

let
Input_Table_Helper_Column = Table.AddColumn(Input_Table, "HelperLPInput", each
Text.Combine(List.Select({
Record.Field(_, Input_Table_Column1)?,
Record.Field(_, Input_Table_Column2)?,
Record.Field(_, Input_Table_Column3)?,
Record.Field(_, Input_Table_Column4)?,
Record.Field(_, Input_Table_Column5)?
}, each _ <> null), " "), type text),

Thanks again

[Mic1979]

Hello @DataNinja777 

did you have the chance to review my previous message?

Thanks.

[ThxAlot]

You're looking to concatenate all non-null fields in each row?

= Table.AddColumn(Input_Table, "HelperLPInput", each Text.Combine(List.Transform(Record.ToList(_), Text.From), ", "))

[Mic1979]

Hello @ThxAlot ,

no my target is to make a custom function working fine even if I get null input parameters.

I have different cases where the custom function I posted can work with.

Hoping this clarifies.

Thanks.