Create new columns from unique IDs in one column

[shaggy101]

Hi there,

I'd like to go from this:

Team	Role	Name
Red	Supervisor	John
Red	Manager	Jerry
Red	Employee	Jed
Orange	Supervisor	Barry
Orange	Manager	Bill
Orange	Employee	Bob

To this:

Team	Manager	Supervisor	Employee
Red	Jerry	John	Jed
Orange	Bill	Barry	Bob

For a very large spreadsheet with many different unique "Roles"

Group By doesn't seem to be getting there.

Sorry for newbie question - deep searching of this forum didn't yield an answer but I'm probably searching poorly.

[adudani · Accepted solution]

hi @shaggy101 ,

create a blank query, copy paste the below code into the advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkpNUdJRCi4tSC0qyyzOLwJyvPIz8pRidWByvol5iempYInUoqJKJBnX3IKc/MrUVLBUCljCvygxLz0V3USnRJhGuDzCVKfMnBxUOSRznfKTlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Team = _t, Role = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Team", type text}, {"Role", type text}, {"Name", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Role]), "Role", "Name")
in
    #"Pivoted Column"

 output:

For reference:

Transpose, pivot or unpivot in Power Query? - YouTube