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

Question

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

Follow the steps in this answer https://stackoverflow.com/questions/44058355/powerquery-how-can-i-concatenate-grouped-values then split the last column by delimiter, and transpose — kevin, Jun 12 '23 at 11:21

score 5 · Accepted Answer · answered Jun 12 '23 at 11:16

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"

score 3 · Answer 2 · answered Jun 12 '23 at 17:37

Just as an exercise, without grouping:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Ans = let s1=Table.Column(Source, "Subjects"), 
              s2=List.Sort(List.Distinct(s1)), 
              n1=Table.Column(Source, "Names"), 
              x=List.Numbers(0,List.Count(n1)), 
              n2=List.Transform(s2,(y)=>List.Accumulate(x,{},(s,c)=>if s1{c}=y then List.Combine({s,{n1{c}}}) else s)),
              m=List.Numbers(0,List.Max(List.Transform(n2,(z)=>List.Count(z)))),
              r1=List.Transform(m,(q)=>Record.FromList(List.Accumulate(s2,{},(s,c)=>List.Combine({s,{try n2{List.PositionOf(s2,c)}{q} otherwise null}})),s2)),
              t1=Table.FromRecords(r1)
          in t1
in
    Ans

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

2 Answers2