Forum Discussion

Anonymous

Not applicable

2 years ago

Solved

Epression.Evaluate in combination with Text.Combine

Hi, I have a function that adds a column to my table. This column contains the concatenation of some other columns i.e. (myTable) => let #"Inserted Merged Column" = Table....

Need Help

ronrsnfld

2 years ago

And here is a modification using a list of columns as an additional argument:

(myTable as table, mergCols as list) => 
    let        
        #"Inserted Merged Column" = Table.AddColumn(myTable, "Merged", (r)=>
            Text.Combine(
                List.Transform(
                    Record.FieldValues(
                        Record.SelectFields(
                            r,mergCols)), 
                    each Text.From(_,"en-BE"))
                ," | "), type text),

        Result= #"Inserted Merged Column" 
    in
        Result

If you named this : fn-CombineCols, you could call it like this in your main query:

    #"Added HASH" = #"fn-CombineCols"(#"Changed Type",
       {
        "Capakey", 
        "Partnumber", 
        "Name", 
        "Street", 
        "Number", 
        "Postcode", 
        "Location", 
        "Province", 
        "StartDate", 
        "EndDate"
       })

Examining your existing code more closely, you have already type all of the columns as text. That being the case, you can remove from the function the List.Transform(...) and shorten to:

(myTable as table, mergCols as list) => 
    let        
        #"Inserted Merged Column" = Table.AddColumn(myTable, "Merged", (r)=>
            Text.Combine(
                    Record.FieldValues(
                        Record.SelectFields(
                            r,mergCols))
                ," | "), type text),

        Result= #"Inserted Merged Column" 
    in
        Result

ronrsnfld

Super User

2 years ago

And here is a modification using a list of columns as an additional argument:

(myTable as table, mergCols as list) => 
    let        
        #"Inserted Merged Column" = Table.AddColumn(myTable, "Merged", (r)=>
            Text.Combine(
                List.Transform(
                    Record.FieldValues(
                        Record.SelectFields(
                            r,mergCols)), 
                    each Text.From(_,"en-BE"))
                ," | "), type text),

        Result= #"Inserted Merged Column" 
    in
        Result

If you named this : fn-CombineCols, you could call it like this in your main query:

    #"Added HASH" = #"fn-CombineCols"(#"Changed Type",
       {
        "Capakey", 
        "Partnumber", 
        "Name", 
        "Street", 
        "Number", 
        "Postcode", 
        "Location", 
        "Province", 
        "StartDate", 
        "EndDate"
       })

Examining your existing code more closely, you have already type all of the columns as text. That being the case, you can remove from the function the List.Transform(...) and shorten to:

(myTable as table, mergCols as list) => 
    let        
        #"Inserted Merged Column" = Table.AddColumn(myTable, "Merged", (r)=>
            Text.Combine(
                    Record.FieldValues(
                        Record.SelectFields(
                            r,mergCols))
                ," | "), type text),

        Result= #"Inserted Merged Column" 
    in
        Result

Anonymous

Not applicable

2 years ago

Hi ronrsnfld ,

Just tested your code, it just works like a charm. Many thanx again.

For your interest, updated file can be found on
https://kuleuven-my.sharepoint.com/:x:/g/personal/bart_plessers_kuleuven_be/EXupkPGqSZ9Fm8Sbiwp6E0ABcT2wUX8_gblf1E86OQtiTw

I have created a generic function that creates a list of columnnames, needed for the merge operation:

(myTable as table, myColumn as text) => 
let
    #"Source" = DATADEF,
    #"myColumn" = myColumn,
    #"Filtered Rows" = Table.SelectRows(#"Source", each Record.Field(_, #"myColumn") <> null and Record.Field(_, #"myColumn") <> ""),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{#"myColumn", Order.Ascending}}),
    #"Selected Colum" = #"Sorted Rows"[NAME],
    #"Result" = #"Selected Colum"
in
    #"Result"

And the function that does the actually merge:

(myTable as table, myListColumns as list, myColumn as text) => 
    let

        #"Inserted Merged Column" = Table.AddColumn(myTable, "Merged", (r)=>
            Text.Combine(
                List.Transform(
                    Record.FieldValues(
                        Record.SelectFields(
                            r,myListColumns)), 
                    each Text.From(_ , "en-BE"))
                ," | "), type text),
        #"Renamed Columns" = Table.RenameColumns(#"Inserted Merged Column",{{"Merged", myColumn}}),
        #"Result"= #"Renamed Columns" 
    in
        #"Result"

The main code that generates my table is very straightforward now:

let
    Source = Excel.CurrentWorkbook(){[Name="SampleData_content"]}[Content],
    
    #"Renamed Columns" = Table.RenameColumns(#"Source", #"DATADEF-RENAME"),
    #"Normalized Table" = #"fn-Normalize-Table"(#"Renamed Columns"),
    #"Added HASH" = #"fn-Add-MERGED"(#"Normalized Table", #"fn-Get-Columns"(#"DATADEF-TABLE","HASH"), "HASH" ),
    #"Added IDX" = #"fn-Add-MERGED"(#"Added HASH" , #"fn-Get-Columns"(#"DATADEF-TABLE","IDX"), "IDX" ),
    #"Added PAND" = #"fn-Add-MERGED"(#"Added IDX" , #"fn-Get-Columns"(#"DATADEF-TABLE","PAND"), "PAND" ),
    #"Result" = #"Added PAND" 
in
    #"Result"

Kind regards,

Bart Plessers

Forum Discussion

Epression.Evaluate in combination with Text.Combine

Related content

Group By - Text.Combine

Tables combine

Text.Combine skip nulls

Text.Combine in group by to not combine blank value

Group by with Text.Combine+List.Transform