How do I group common items together within Power Query?

[dinosainsburys]

I am ingesting data where it shows what is installed on a server. There are only 4 options as this has been filtered further upwards the data ingestion pipeline. For example, the 4 options are Word, Excel, PowerPoint, Outlook. 

So my raw table can look like this

Server	Software Installed
SERVER_1	Word
SERVER_1	PowerPoint
SERVER_2	Word
SERVER_2	Outlook
SERVER_3	PowerPoint
SERVER_4	Outlook

What I would want from this is to have seperate columns for Word, PowerPoint, Excel and Outlook with the server names under each. I've tried playing around with 'Group By' but couldn't get it to work. Any ideas? 

[mh2587 · Accepted solution]

• 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).

[V-yubandi-msft]

Hi @dinosainsburys ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[V-yubandi-msft]

Hi @dinosainsburys ,

Thank you for reaching out to the Microsoft Fabric Community.  Both @Jai-Rathinavel  & @mh2587 have provided responses that align well with your requirements. We recommend reviewing their suggestions and trying them out in your environment. If you encounter any issues or need further assistance, please let us know.

Also, thanks to @Jai-Rathinavel  & @mh2587 for the quick and helpful responses.

Regards,

Yugandhar.

[Jai-Rathinavel · Accepted solution]

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

[mh2587 · Accepted solution]

• 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).