Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |