Forum Discussion
Error "There weren't enough elements in the enumeration to complete the operation." when Pivoting
adinezza See attached.
adinezza See attached.
Riny_van_Eekelen Hi Riny, I have a similar issue, see attached. Similar data, but one column with IDs, then a bunch of numbers, I want all the numbers related to ID under it as a column header. Any help appreciated!
You can use group and transpose:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], ChangeType = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Number", Int64.Type}}), grouped = Table.Group(ChangeType, {"ID"}, {{"NumbersGroup", each _, type nullable table[ID = nullable text, Number = nullable Int64.Type]}}), TransposeGroups = Table.AddColumn(grouped, "TransposedTable", each Table.Transpose([NumbersGroup])), KeepNumbers = Table.AddColumn(TransposeGroups, "TransposedNumbers", each Table.LastN([TransposedTable], 1)), combined = Table.Combine(KeepNumbers[TransposedNumbers]), TransposeCombined = Table.Transpose(combined), zipped = List.Zip({Table.ColumnNames(TransposeCombined),grouped[ID]}), renamed = Table.RenameColumns(TransposeCombined, zipped) in renameddavidmaddock54 Thought of a quick-and-dirty way to fix it, though believe there should be a better way to do it.
I didn't dig your query, but it looks like result is not correct
rachel , we may simplify it to
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], GroupedID = Table.Group(Source, {"ID"}, {{"Columns", each [Number]}}), Result = Table.FromColumns( GroupedID[Columns], GroupedID[ID] ) in Resultdavidmaddock54 , if order of numbers in the source doesn't matter, perhaps it's better to sort them in columns as in next query in attached attached
Thank you! the code is so straightforward and self explanatory!
By comparison, the code generated by pressing "Group By" button now looks like a mess!
