Epression.Evaluate in combination with Text.Combine

[bartplessers]

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.AddColumn(myTable, "Merged", each Text.Combine(
        {
            [Capakey], 
            [Partnumber], 
            [Name], 
            [Street], 
            [Number], 
            Text.From([Postcode], "en-BE"), 
            [Location], 
            [Province], 
            Text.From([StartDate], "en-BE"), 
            Text.From([EndDate], "en-BE")
        }, " | "), type text),
        Result= #"Inserted Merged Column" 
    in
        Result

However, I want to mak this more dynamically. The list of columns should come from a powerquery list.

This would result is something like

(myTable) => 
    let
        #"Inserted Merged Column" = Table.AddColumn(myTable, "Merged", each Expression.Evaluate("Text.Combine(myList"), type text),
        Result= #"Inserted Merged Column" 
    in
        Result

but I can't get this working

So to simplify things:

I tried 

(myTable) => 
    let
        #"Inserted Merged Column" = Table.AddColumn(myTable, "Merged", each Expression.Evaluate("1+2"), type number),
        Result= #"Inserted Merged Column" 
    in
        Result

and this works. The expression "1+2" is evaluated, and the result "3" is stored as a number

BUT

if I try to do the same thing with Text.Combine

(myTable) => 
    let
        #"Inserted Merged Column" = Table.AddColumn(myTable, "Merged", each Expression.Evaluate("Text.Combine({""xxx"", ""yyy""}, "" | "")"), type text),
        Result= #"Inserted Merged Column" 
    in
        Result

this doesn't seem to work.

So my question:

How can I add a column that is the concatination of a list of other columns.

It is more clear in my example attached:

- I have a tab "Sample Data". This is used for input in PowerQuery

- the tab "parcels" is generated with PQ

- this is done by doing some manipulations on the sample data. More specific: the action "Added HASH" calls the function #"fn-Add-HASH"( )

- in this function, the concatenation is done by manually defining the columns that should be concatenated:

        #"Inserted Merged Column" = Table.AddColumn(myTable, "Merged", each Text.Combine(
        {
            [Capakey], 
            [Partnumber], 
            [Name], 
            [Street], 
            [Number], 
            Text.From([Postcode], "en-BE"), 
            [Location], 
            [Province], 
            Text.From([StartDate], "en-BE"), 
            Text.From([EndDate], "en-BE")
        }, " | "), type text),

and of course, this works.

But I want to make this dynamic.

So I have a tab "DATADEF" where in column F the user can decide which column they want to concatenate and in which order by filling in a number.
This results in a PQ list "DATADEF-HASH": 

So, the big question is: how can I use the automatically generated list "DATADEF-HASH" as input for Text.Combine in the function "fn-Add-HASH"...

any help is appreciated!

Kind regards,

Bart

[bartplessers]

"wowkes"!

Don't know the english word for this, but in Dutch it means: incredible, fantastic, flabbergasted, ...

This seems exactly what I needed. Will try this monday!

Thanx a lot!!!

[ronrsnfld · Accepted solution]

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

[bartplessers]

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/EXupkPGqSZ9Fm8Sbiwp6E0AB...

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

[spinfuzer]

What is this doing that the Merge columns function in the GUI does not already do?

Select every column, right click on a column header, and click on Merge.

Alternatively, selecy all columns, go to Transform or Add Column --> Merge Columns.

[ronrsnfld]

It is allowing the list of columns to be merged to come from a list. As he wrote: "I want to mak this more dynamically. The list of columns should come from a powerquery list."

His actual code creates a number of hashes of different combinations of columns.

[bartplessers]

mmm. Seems I can not upload an excel file.

But you can find it here

Parcels - WEBSERVICE - DEBUG.xlsx

[ronrsnfld]

If I understand you correctly, somthing like:

(myTable) => 
    let
        #"Merge List" = {"Capakey", "Name","Province"},    
        
        #"Inserted Merged Column" = Table.AddColumn(myTable, "Merged", (r)=>
            Text.Combine(Record.FieldValues(Record.SelectFields(r,#"Merge List"))
                , " | "), type text),
        Result= #"Inserted Merged Column" 
    in
        Result

Of course, you could add the "List" as an argument to the function.

This may be more flexible, converting any numbers to text:

(myTable) => 
    let
        #"Merge List" = {"Capakey", "Name","Province"},    
        
        #"Inserted Merged Column" = Table.AddColumn(myTable, "Merged", (r)=>
            Text.Combine(
                List.Transform(
                    Record.FieldValues(
                        Record.SelectFields(
                            r,#"Merge List")), 
                    each Text.From(_))
                ," | "), type text),
                
        Result= #"Inserted Merged Column" 
    in
        Result