Forum Discussion
Help with Pivoting or Transposing columns with unaggregated multiple values
I have a liist of networks and a list of groups that are found on thoe networks:
| Network | Group_Name |
| 10.100.100.10 | HR |
| 10.100.100.11 | HR |
| 10.100.100.11 | IT |
| 10.100.100.11 | Logistics |
| 10.100.100.12 | HR |
| 10.100.100.12 | Logistics |
| 10.100.100.13 | IT |
If a networks has multiple groups, I'd like to get them on the same line and cell:
| Network | Group_Names |
| 10.100.100.10 | HR |
| 10.100.100.11 | HR, IT, Logistics |
| 10.100.100.12 | HR, Logistics |
| 10.100.100.13 | IT |
I tried to Pivot with unaggregated values, so I could get everything on one line and do a custom column with all the column values, but I get an error when I try to pivot, it won't do more than one row. In hindsight this might not have been a good solution anyway (though I might have had a way to plow through it) as the data source will be updated from time to time and the number of maximum groups will fluctuate. Current record is over 30.
// Table let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQMzSAYSUdJY8gpVgdVGFD3MKeIViFffLTM4tLMpOLMWSNsJtlhFeTMdSmWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Network = _t, Group_Name = _t]), #"Grouped Rows" = Table.Group(Source, {"Network"}, {{"Groups", each Text.Combine(_[Group_Name],",")}}) in #"Grouped Rows"Hi ABR002,
Thank you for reaching out to the Microsoft Fabric Forum Community.I’ve reproduced your scenario using my sample data and I was able to achieve the expected output. Here's a summary of the steps I took:
- Grouped the data by Network and collected the Group_Name values into a list under the AllGroups column.
- Created a custom column to concatenate the Group_Name values using the following M code:
Text.Combine(List.Transform([AllGroups][Group_Name], each Text.From(_)), ", ")For your reference, I’ve attached a screenshot of the output and the .pbix file with the solution.
Thank you, lbendlin for sharing your valuable insights.
If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.
2 Replies
// Table let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQMzSAYSUdJY8gpVgdVGFD3MKeIViFffLTM4tLMpOLMWSNsJtlhFeTMdSmWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Network = _t, Group_Name = _t]), #"Grouped Rows" = Table.Group(Source, {"Network"}, {{"Groups", each Text.Combine(_[Group_Name],",")}}) in #"Grouped Rows"Hi ABR002,
Thank you for reaching out to the Microsoft Fabric Forum Community.I’ve reproduced your scenario using my sample data and I was able to achieve the expected output. Here's a summary of the steps I took:
- Grouped the data by Network and collected the Group_Name values into a list under the AllGroups column.
- Created a custom column to concatenate the Group_Name values using the following M code:
Text.Combine(List.Transform([AllGroups][Group_Name], each Text.From(_)), ", ")For your reference, I’ve attached a screenshot of the output and the .pbix file with the solution.
Thank you, lbendlin for sharing your valuable insights.
If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.