Help with Pivoting or Transposing columns with unaggregated multiple values

[ABR002]

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.

[lbendlin · Accepted solution]

// 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"

[v-ssriganesh · Accepted solution]

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.

[lbendlin · Accepted solution]

// 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"