Forum Discussion

shanebo3239's avatar
shanebo3239
Helper I
8 years ago
Solved

Pivoting Data Question

I have an import from a web page that looks something like this:   Col1 | Col2 ID | 1 Name | John Age | 33 ID | 2 Name | Sam Age | 12 ID | 3 ...
Need Help
  • Vvelarde's avatar
    Vvelarde
    8 years ago

    shanebo3239

     

    Hi, you need a column to applied the pivot.

     

    Please follow these steps.

     

     

    Regards

     

    Victor

    Lima - Peru

  • shanebo3239's avatar
    shanebo3239
    8 years ago

    Victor,

     

    That is a much simpler solution than what I ended up doing (right before you posted, unfortunately!)

     

    Anyway, here's what I ended up with.  First, I did a group by in query editor and grouped by Col1.  That left me with a "List" link in Col2, which is now called GR1.  Then, using advanced editor I did this:

     

    Table.PromoteHeaders(Table.FromColumns(Table.Group(Col1, {{"GR1", each List.InsertRange([Col2],0,List.Distinct([Col1]))}})[GR1]))

     

    Not pretty, but it works.

     

    Thank you so much!

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi shanebo3239,

 

This M code works

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Partition = Table.Group(Source, {"Col1"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Col2", "Index"}, {"Col2", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Partition", List.Distinct(#"Expanded Partition"[Col1]), "Col1", "Col2"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

Hope this helps.