Create new columns from unique IDs in one column

Question

Hi there,I'd like to go from this:TeamRoleNameRedSupervisorJohnRedManagerJerryRedEmployeeJedOrangeSupervisorBarryOrangeManagerBillOrangeEmployeeBobTo this:TeamManagerSupervisorEmployeeRedJerryJohnJedOrangeBillBarryBobFor 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 Answer

hi&nbsp;shaggy101&nbsp;,
&nbsp;
create a blank query, copy paste&nbsp;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"
&nbsp;
&nbsp;
&nbsp;output:

&nbsp;
&nbsp;
For reference:
&nbsp;
Transpose, pivot or unpivot in Power Query? - YouTube&nbsp;
&nbsp;
&nbsp;

Forum Discussion

Create new columns from unique IDs in one column

1 Reply

Related content

Create 3 digit sequential unique key

Create unique ID

Create columns group

Unique number column

Create Measure for Unique values in a column

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