Search code examples
excelpowerbipowerquerypowerbi-desktopm

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

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.

    enter image description here

    enter image description here

    enter image description here

    enter image description here

    enter image description here

    enter image description here

    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"