Pivoting Data Question

[shanebo3239]

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!

[Vvelarde · Accepted solution]

@shanebo3239

Hi, you need a column to applied the pivot.

Please follow these steps.

Regards

Victor

Lima - Peru

[Ashish_Mathur]

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.

[Vvelarde · Accepted solution]

@shanebo3239

Hi, you need a column to applied the pivot.

Please follow these steps.

Regards

Victor

Lima - Peru

[shanebo3239 · Accepted solution]

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!