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 Everyone,
Can someone please help me with the below scenario. I have a table which contains two columns like below.
House Name
1 | ABC |
1 | DEF |
1 | GHI |
2 | JKL |
2 | MNO |
3 | PQR |
4 | STU |
4 | VWX |
Now what I'm trying to achieve is:
House Name
1 | ABC, DEF, GHI |
2 | JKL, MNO |
3 | PQR |
4 | STU, VWX |
Solved! Go to Solution.
Hi @Anonymous
You can create a new calculated table as follows, where Table1 is the upper table you show:
NewTable = ADDCOLUMNS ( DISTINCT ( Table1[House] ), "Name", CALCULATE ( CONCATENATEX ( DISTINCT ( Table1[Name] ), Table1[Name], ", " ) ) )
Here is a Power Query way to do it:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0claK1YGwXVzd4Gx3D08w2wjI9vL2gbN9/fzBbGMgOyAwCMw2AbKDQ0Lh7LDwCKXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [House = _t, Name = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"House", Int64.Type}, {"Name", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"House"}, {{"Name", each _, type table}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.ToList(Table.SelectColumns([Name],"Name"))), #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}), #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Name"}) in #"Removed Columns"
Here is a Power Query way to do it:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0claK1YGwXVzd4Gx3D08w2wjI9vL2gbN9/fzBbGMgOyAwCMw2AbKDQ0Lh7LDwCKXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [House = _t, Name = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"House", Int64.Type}, {"Name", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"House"}, {{"Name", each _, type table}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.ToList(Table.SelectColumns([Name],"Name"))), #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}), #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Name"}) in #"Removed Columns"
Hi @Anonymous
You can create a new calculated table as follows, where Table1 is the upper table you show:
NewTable = ADDCOLUMNS ( DISTINCT ( Table1[House] ), "Name", CALCULATE ( CONCATENATEX ( DISTINCT ( Table1[Name] ), Table1[Name], ", " ) ) )
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |