Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
bvbull200
Helper III
Helper III

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

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 NumberSalesperson NumberJob Function
10001123ZI
10002456ZI
10002789ZO
10003183ZI

 

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 NumberInside SalespersonOutside Salesperson
10001123--
10002789456
10003183--

 

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.

1 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.

View solution in original post

6 REPLIES 6
bvbull200
Helper III
Helper III

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:

 

bvbull200_0-1600282397622.png

 

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.

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.

v-lionel-msft_0-1600668110418.png

v-lionel-msft_2-1600668163436.pngv-lionel-msft_3-1600668201295.png

 

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.

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
Super User
Super User

@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

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

You deserve a kudos!

m

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. 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.