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
Name     |   Tom
Age      |   22


How would I get that data via the query editor to look like this:

 

ID |  Name  | Age
1  |  John  | 33
2  |  Sam   | 12
3  |  Tom   | 22

I feel like this is so simple, but every time I pivot the data and select "Dont Aggregate", I get " There were too many elements in the enumeration to complete the operation." 

Thank you!

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!

3 Replies

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    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
      Helper I
      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.