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,
Im trying to aggregate data as follow:
Current data format:
ID Name
1 A
1 B
1 C
2 D
2 E
3 F
Expected result:
ID Name
1 A;B;C
2 D;E
3 F
Do you know how to build this step in my query?
Best regards,
Solved! Go to Solution.
Hi @Klaimand,
Based on my test, you could refer to below formula:
Create a new table:
New Table = SUMMARIZE('Table1','Table1'[ID],"Name",CONCATENATEX('Table1','Table1'[Name],","))
Result:
You could also download the pbix file to have a view.
Regards,
Daniel He
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}}), Combine = Table.Group(#"Changed Type", {"ID"}, {{"All Names", each Text.Combine(List.Distinct([Name]), "; "), type text}}) in Combine
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}}), Combine = Table.Group(#"Changed Type", {"ID"}, {{"All Names", each Text.Combine(List.Distinct([Name]), "; "), type text}}) in Combine
Hi @Klaimand,
Based on my test, you could refer to below formula:
Create a new table:
New Table = SUMMARIZE('Table1','Table1'[ID],"Name",CONCATENATEX('Table1','Table1'[Name],","))
Result:
You could also download the pbix file to have a view.
Regards,
Daniel He
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 | |
97 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |