How do I group common items together within Power Query?

 

• Load your table into Power Query.

• Go to the Home tab → click on "Pivot Column".

• In the Pivot Column window:

  • Choose Software Installed as the column to pivot.

  • Use Server as the values column.

  • In the advanced options, use Don't Aggregate (or use Max if you don't see that).

• This will give you what you need — a column for each software, showing the server names.

• Optionally, if servers have multiple installs and repeat, you'll need to:

  • First remove duplicates (Server, Software Installed) if needed.

  • Use the "Max" aggregation (this works because it will show the server name as a string if there's only one per cell).

 

Hi dinosainsburys  Please use the below M Code and replace the Source line with your original Source. I have also provided the snap of Input table and Output Table below.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCnYNCnMNijdU0lEKzy9KUYrVQRELyC9PLQrIz8wrQZYxwqIaJOZfWpKTn5+NLGyM0xATZA2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Server = _t, #"Software Installed" = _t]),
    Grouped = Table.Group(Source, {"Software Installed"}, {{"Servers", each [Server], type list}}),
    MaxCount = List.Max(List.Transform(Grouped[Servers], List.Count)),
    Transposed = List.Transform({0..MaxCount - 1}, each 
        Record.FromList(
            List.Transform(Grouped[Servers], (s) => if List.Count(s) > _ then s{_} else null),
            Grouped[Software Installed]
        )
    ),
    Result = Table.FromRecords(Transposed),
    #"Changed Type" = Table.TransformColumnTypes(Result,{{"Word", type text}, {"PowerPoint", type text}, {"Outlook", type text}})
in
    #"Changed Type"

 

Input:

 

Output:

 

Hope it Helps 🙂 Appreciate a Kudos !

 

Thanks,

Jai Rathinavel | LinkedIn