Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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),"-"))
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.
Thank you Guys!
This is awesome.
Jacek
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.
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),"-"))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |