Power Query – Columns as separate unrelated list (from unpivoted data)

June 12, 2023

If I have a simple unpivoted list like this:

Names	Subjects
John	English
Sam	Art
Fred	Biology
John	Biology
Emma	Art
Mike	Biology

How can I create a list like this where the rows are unrelated to each other and each column is almost a separate list? Pivoting doesn’t work – is this possible?

Art	Biology	English
Emma	Fred	John
Sam	John	null
null	Mike	null

Thanks

>Solution :

You need to group and add an index before pivoting.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Names", type text}, {"Subjects", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Subjects"}, {{"All", each _, type table [Names=nullable text, Subjects=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([All], "Index")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"All"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Names", "Index"}, {"Names", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[Subjects]), "Subjects", "Names"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns1"