Forum Discussion

adinezza's avatar
adinezza
Copper Contributor
Apr 01, 2022
Solved

Error "There weren't enough elements in the enumeration to complete the operation." when Pivoting

I have a .txt file that I am running through Power Query and into Excel.  The txt file is a simple format that has an item ID on one line and an error message on the next, eg: Item ABC Error Msg I...
excel
power bi
davidmaddock54's avatar
davidmaddock54
Brass Contributor
Apr 30, 2024

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!

 

 

rachel's avatar
rachel
Iron Contributor
Apr 30, 2024

davidmaddock54 

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
    renamed

 

Resources