Dynamically concatenate columns from table

[jaryszek]

Hello,

i have table in Excel like here:

and second table is :

ListOfTables - there is a list for each table which columns i want to concatenate automatically in order to get Added column with result (for Table1): "Col1-Col2-Col3". 

Now to create custom column i have to manually input formula :

How can i do this automatically? From list? 

Please help,
Jacek

[Vera_33 · Accepted solution]

Hi @jaryszek 

The column content or the column name you want to concat?

Text.Combine( List.Difference(Record.ToList(#"Added Index"{[Index]}),{[Index]}),"-"))

Text.Combine( Table.ColumnNames(Source),"-"))

[jaryszek]

Thank you Guys! 

This is awesome.
Jacek

[v-alq-msft · Accepted solution]

Hi, @jaryszek 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table1:

Table2:

You may apply the following steps 'Grouped Rows', 'Expanded Columns', 'Changed Type1' steps. Here are the m codes in 'Advanced Editor' for 'Table2'.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCklMykk1VNJRcs7PMVSK1UEVMcIQMUaIGGHoMsLQZYRLl4lSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TableName = _t, ColumnsToConcatenate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TableName", type text}, {"ColumnsToConcatenate", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"TableName"}, {{"Columns", each 
Table.AddColumn(
    Table.SelectColumns( Table1,[ColumnsToConcatenate]),
    "New",
    (x)=>Text.Combine(Record.ToList(x),"-")
)
    
     }}),
    #"Expanded Columns" = Table.ExpandTableColumn(#"Grouped Rows", "Columns", {"Col1", "Col2", "Col3", "Col4", "New"}, {"Col1", "Col2", "Col3", "Col4", "New"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Columns",{{"Col1", type text}, {"Col2", type text}, {"Col3", type text}, {"Col4", type text}, {"New", type text}})
in
    #"Changed Type1"

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vera_33 · Accepted solution]

Hi @jaryszek 

The column content or the column name you want to concat?

Text.Combine( List.Difference(Record.ToList(#"Added Index"{[Index]}),{[Index]}),"-"))

Text.Combine( Table.ColumnNames(Source),"-"))