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

[adinezza]

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?

[#2 · Accepted solution]

@adinezza See attached.

[#3]

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?

[#4]

@adinezzaYour method probably works also if just add an index column.

[#5]

@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!

[#6]

@davidmaddock54 Thought of a quick-and-dirty way to fix it, though believe there should be a better way to do it.

[#7]

@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

[#8]

@Riny_van_Eekelen 

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
    Result

@davidmaddock54 , 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

[#9]

@SergeiBaklan Oops! Must have been confused.

[#10]

@SergeiBaklan 

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!