Group By column to unpivot

[imnotabot01001]

Hi all!

I have a table like such:

ID	Item	Color
a123	001	red
a123	002	blue
b456	001	blue
c789	001	red
c789	002	blue
c789	003	purple

and I need it to be grouped, then "unpivoted" like so:

ID	Item1	Color1	Item2	Color2	Item3	Color3	Item4	Color4
a123	001	red	002	blue	null	null	null	null
b456	001	blue	null	null	null	null	null	null
c789	001	red	002	blue	003	purple	null	null

How would I be able to do this? 

edit: there's a similar question here https://community.powerbi.com/t5/Desktop/Group-by-a-column-with-expansion/m-p/504490#M235507 , but doesn't count for irregular item counts down the ID. would appreciate any and all help! 😉

[Anonymous]

iin this file (tab3) you find a parametric solution which manage case where you have a genric numbers of items(colours)

[Anonymous · Accepted solution]

I wonder whether a transformation of this type (second query in the file) is no longer useful.

But this can only be known by you depending on what you have to do next.

This solution can be simplified (see query ----2a)