How to Fix: [expression.error] there were too many elements in the enumeration to complete

[bvbull200]

I have a report that has been set up and running fine for about 6 months, now.  Recently, one of the SQL tables that is part of this report was updated.  It is a table that basically tells which salesperson is assigned to which account.  We used to have a number of unassigned accounts, but now they all have an assignment.

That table is listed in basically this format:

Account Number	Salesperson Number	Job Function
10001	123	ZI
10002	456	ZI
10002	789	ZO
10003	183	ZI

ZI is an inside salesperson and ZO is an outside salesperson.  All accounts have a ZI, but only some have a ZO.  That is pivoted in its originally query to look like this (after some column renaming and such):

Account Number	Inside Salesperson	Outside Salesperson
10001	123	--
10002	789	456
10003	183	--

That is the query result that gets merged in to my large query that includes transactional data.

Just yesterday, I tried to refresh and started throwing the following error:

ole db or odbc error: [expression.error] there were too many elements in the enumeration to complete the operation

If I take off any refreshes relating to the query that has the salesperson data, I can refresh, but that query and/or any query in to which it is merged will fail to refresh.

I cannot begin to understand where I should look for the error since it isn't defined for me.  I have pulled that SQL table in to a .txt file and I can pivot it in Power Query in Excel no problem.  I evaluated the results (about 275k rows) and did not find any anomalies that stood out (i.e. text in a number field).  No duplicate rows, either. 

Where should I be looking to resolve this problem?  What is the error trying to communicate?  What minor change to the data would have all of a sudden thrown this error?

I appreciate your input.

[bvbull200]

Some additional info:

I cannot throw the error in Power Query.  I go through all of the steps for this query and it throws no error.  It is only when I try to refresh that I get the error on the query which contains the merged data. If I remove these steps, it works:

Those have been in place from day 1 and only just now are causing an issue.  I can't seem to work around it.  If I had some way of knowing what the error acutally means or where to look for the problem, I'd be better off, but I can't find that anywhere.

Desperate for guidance.

[v-lionel-msft]

Hi @bvbull200 ,

Is it due to the continuous update of your data that the merged query contains duplicate columns?

The current way I can reproduce this error is to do it like the following.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bvbull200 · Accepted solution]

I don't think that is it, either. I import this data, not query against it directly. The data changes very infrequently, too, and only during overnight batch processes, so it wouldn't be going on during my data load.

I don't know how/why this fixed it, but what I did was reverse the steps of the query merge and the pivoting of columns. Like a multiplication problem, the order of the factors was irrelevant to the end result, but when I switched them around, I'm getting the data to load properly and without that error.

I wish I better understood the error to be able to explain why that works (especially since the error didn't follow any change to the design), but at least it's running.

[nandic]

@bvbull200 ,

Check out this link, hope it will help.
It has nice explanation and steps to resolve the problem.
https://www.youtube.com/watch?v=aTXQ77Vf5Gc

[maclura]

Thanks @nandic for sharing that great video, very instructive.

You deserve a kudos!

m

[bvbull200]

Thank you for the suggestion.

I have watched that video in my research, but I do not think it applies to my situation?  I can pivot the columns just fine (and have done so for the last 6 months).  They appear is the right order with the right data available.  It is when I merge that query with another that I end up with a step that is throwing this error.