Forum Discussion
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
Item DEF
Error Msg
Item GHI
Error Msg
...
Ultimately, what I want is one column with the Item ID, and one with the error message. Simple enough. So after I pull the txt file in, I add a custom column :
if Text.Start([Column1],4) = "Item" then "ITEM ID" else "ERROR MSG"
This then give me alternating rows of "ITEM ID" and "ERROR MSG". Then I pivot on the new column using the original column as the Values Column and "Don't Aggregate" for the function, expecting to get my final result. However all I get is a table with two error cells and the error is "There weren't enough elements in the enumeration to complete the operation."
How can I fix this or is there another way to accomplish this simple task in Power Query?
adinezza See attached.
9 Replies
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 renamed
- Thank you! I was able to use your method on my file, and it worked.
Do you know why my method gives the error, though? Do you need at least 3 columns for the pivot function to work?adinezzaYour method probably works also if just add an index column.
