Forum Discussion

Mic1979's avatar
Mic1979
Post Partisan
1 year ago
Solved

Customer Function Table.Combine

Dear all,

 

I would need your support to solve this basic problem.

 

I have this Custom Function:

 

(
Input_Table as table,
ColumnToRemove1 as text,
ColumnToRemove2 as text,
ColumnToRemove3 as text,

InputColumn1 as text,
InputColumn2 as text,
InputColumn3 as text,
InputColumn4 as text
) =>

let
SourceNoNull = Table.ReplaceValue(
Input_Table,
null,
(x)=>x,
(x,y,z)=>
if x = null
then ""
else x,
Table.ColumnNames(Input_Table)),

RemovedColumn = Table.RemoveColumns(
SourceNoNull,{
ColumnToRemove1,
ColumnToRemove2,
ColumnToRemove3
}),

Helper_1 = Table.AddColumn(RemovedColumn, "Helper_1", each Text.Combine({InputColumn1, InputColumn2}, ""), type text)

 

in Helper_1

 

and this is Invoked in this way:

 

let
Source = Excel.CurrentWorkbook(){[Name="Connection_Type_DISTRIBUTION"]}[Content],

ChangeDistribution = Distribution_Manipulation(
Source,
"Y3",
"MKT_Distribution",
"Split_per_Step",

"Region",
"Project_Step",
"Function_Description",
"Distribution"
)
in
ChangeDistribution

 

The part withAddColumn does not do what I want. 

 

Here what I have when I inoke the function:

 

However I want to have this:

 

Probably I am doing something not correct when I am passing the parameters to the function.

 

Thanks for your support

Need Help
  • suparnababu8's avatar
    suparnababu8
    1 year ago

    Hi Mic1979 

     

    Try with this 

    (
Input_Table as table,
ColumnToRemove1 as text,
ColumnToRemove2 as text,
ColumnToRemove3 as text,

InputColumn1 as text,
InputColumn2 as text,
InputColumn3 as text,
InputColumn4 as text
) =>

let
    // Replace null values with empty strings
    SourceNoNull = Table.ReplaceValue(
        Input_Table,
        null,
        "",
        Replacer.ReplaceValue,
        Table.ColumnNames(Input_Table)
    ),

    // Remove specified columns
    RemovedColumn = Table.RemoveColumns(
        SourceNoNull,
        {ColumnToRemove1, ColumnToRemove2, ColumnToRemove3}
    ),

    // Combine values from InputColumn1 and InputColumn2 into a new column "Helper_1"
    Helper_1 = Table.AddColumn(RemovedColumn, "Helper_1", each Text.Combine({Record.Field(_, InputColumn1), Record.Field(_, InputColumn2)}, ""), type text)

in
    Helper_1

3 Replies

  • suparnababu8's avatar
    suparnababu8
    Super User
    1 year ago

    Hi Mic1979 

     

    Try with this 

    (
Input_Table as table,
ColumnToRemove1 as text,
ColumnToRemove2 as text,
ColumnToRemove3 as text,

InputColumn1 as text,
InputColumn2 as text,
InputColumn3 as text,
InputColumn4 as text
) =>

let
    // Replace null values with empty strings
    SourceNoNull = Table.ReplaceValue(
        Input_Table,
        null,
        "",
        Replacer.ReplaceValue,
        Table.ColumnNames(Input_Table)
    ),

    // Remove specified columns
    RemovedColumn = Table.RemoveColumns(
        SourceNoNull,
        {ColumnToRemove1, ColumnToRemove2, ColumnToRemove3}
    ),

    // Combine values from InputColumn1 and InputColumn2 into a new column "Helper_1"
    Helper_1 = Table.AddColumn(RemovedColumn, "Helper_1", each Text.Combine({Record.Field(_, InputColumn1), Record.Field(_, InputColumn2)}, ""), type text)

in
    Helper_1
  • Mic1979's avatar
    Mic1979
    Post Partisan
    1 year ago

    Many thanks.

     

    based on what you said, I tried this other function:

     

    (
    Input_Table as table,
    SeriesColumn as text,
    Port12Column as text,
    Port3Column as text
    ) =>

    let
    RULE_1_1 = Table.ReplaceValue (
    Input_Table,
    each Port3Column,
    each if SeriesColumn = "2 way Angle Body Valve" and
    Port12Column = "1st and 2nd Ways Threaded" and
    Port3Column = "NOT APPLICABLE"
    then "NOT APPLICABLE"
    else Port3Column,
    Replacer.ReplaceText,
    Table.ColumnNames(Input_Table) = "Port3Column"
    )

    in RULE_1_1

     

    It gives an error message, not sure what is notgoing fine here.

    Any suggestion?

     

    Thanks.

    • Mic1979's avatar
      Mic1979
      Post Partisan
      1 year ago

      This is the error message I got:

       

      I think I still did not get properly how to pass parameters ğŸ˜”.

       

      Thanks.