Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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.
Solved! Go to 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.
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.
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.
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.
@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
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
88 | |
85 | |
82 | |
65 | |
49 |
User | Count |
---|---|
138 | |
110 | |
104 | |
66 | |
64 |