Forum Discussion

Mic1979's avatar
Mic1979
Post Partisan
1 year ago
Solved

null input parameters custom function

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...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Mic1979 
    could you please provide sample of your tables?

     

ZhangKun's avatar
ZhangKun
Super User
1 year ago

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's avatar
dufoq3
Community Champion
1 year ago

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's avatar
    Mic1979
    Post Partisan
    1 year ago

    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's avatar
      dufoq3
      Community Champion
      1 year ago

      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's avatar
        Mic1979
        Post Partisan
        1 year ago

        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.