There were too many elements in the enumeration to complete - No pivot but there is a depivot

[MarkD1722]

I got a new error after making changes to my query.  I had a step to advance a date of interest (DATE _1) by a known duration.  Originally, the duration was calculated with #duration(d, h, m, s) function using a rounded value for the "d" value (i.e., days)... thus ending up with #duration(d,0,0,0).  Due to the occasional inaccuracy of that rounding, I had to subsequently determine the exact duration by calculating new days (d), hours (h), minutes (m), and seconds (s) and update the step that advances DATE_1.   After updating DATE_1, I use the new date to make a bunch of new columns.  Then I depivot on all those new columns to ultimately make a table of only two columns. 

The process prior to this change in duration calculation has been working for over two months--so the process on the whole works.  I didn't think this change should change anything, but when I went to apply this updated query, I got the following error:

"OLE DB or ODBC error: [Expression.Error] There were too many elements in the enumeration to complete the operation."

My research into this problem, I found solutions that say to add an index column before pivoting...  The problem is that I am not pivoting anything.  Rather, I am depivoting to get those new columns into a single columns from which I can filter.  Does this index solution work here?  I am going to try it, but I don't understand why it would work if it does.

Any thoughts on a solution to this?

[MarkD1722]

Thank you, @AmiraBedh for your response.  So, I thought the same and had worked out the query so that I as soon as I create the new DATE_2, I remove all the columns I needed for that calculation.  This is way before the depivot and would technically bring me right back to just one new column before adding all my other columns on which I want to depivot.   Please recall that my process was working just fine with DATE_2 simply being calculated a different way (I rounded the number which cause some edge case inaccuracies), and there are no new rows created in my query.   Is there a way to determine which step within the query is causing too many enumerations, because I don't get any errors until I try to apply the model.

I will give the index step a try, but I don't understand what that is doing to help.  If someone can help explain how that is working to solve, I would appreciate it.

Thank you!

[AmiraBedh]

If you share some data I can dig deeper 🙂

[MarkD1722]

@AmiraBedh  

Using a duplicate data model, I did add an Index prior before the UNPIVOT step, but even with that index, the enumeration error was still there.  Then I decided to refactor my query and removed a bunch of unnecessary columns up front before doing the 15 or so new columns (using that new DATE_2) that I needed before unpivoting. That did the trick.  I still don't understand why however.  I have not removed the Index column step, but I do remove the index column at the very end.

Bottom line is that I got the model to work... now to implement it in the actual data model.

Thank you for your thoughts and suggestions on this!

[AmiraBedh · Accepted solution]

I think you are getting that error becauseof the large number of columns or rows being processed.

Before depoviting, try with adding  an index column :

= Table.AddIndexColumn(YourPreviousStep, "Index", 1, 1, Int64.Type)

Then depivot :

= Table.UnpivotOtherColumns(PreviousStep, {"Index"}, "Attribute", "Value")

Just a recommendation, remove any columns that you don't need in advance.