null input parameters custom function

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,

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).

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.

Hello

I did not solve the issue..

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.