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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sebbyp
Helper III
Helper III

Expression.Error: There weren't enough elements in the enumeration to complete the operation

What does this error mean?  I am collecting data from using a web connector.  Any help much appreciated.

1 ACCEPTED SOLUTION
v-sihou-msft
Employee
Employee

@sebbyp

 

This error is thrown when pivoting columns with same name. Please check your column names in your data source. Also refer to a similar thread below:

 

https://social.technet.microsoft.com/Forums/en-US/7923e025-a0c4-407b-bd0f-65959e5038bb/what-does-thi...

 

Regards,

View solution in original post

21 REPLIES 21
stefancubes
New Member

Hi, 

 

We also got this error when referencing a dataflow in a workspace without the proper credentials. It was resolved by editing the credentials in the 'Manage gateways and connections' >> 'Data Sources' tab.

 

We were using an OAuth2 connection.

 

Hope this helps!

MartinPerson
New Member

To add to the pool of experience:

My situation was I was connected to a folder. Within that folder I only wanted the top 2 files sorted by create date in descending order. The folder is expected to only contain Excel files (.xlsx), and someone saved a text file (.txt) to the folder. Removing the text file from the folder resolved my issue.

amcox
Employee
Employee

This is a silly error but just in case it's useful to future users - this error is also what you will get when you reference a folder as your data source and there are no files within that folder.

Lachlan_Duncan
Advocate I
Advocate I

It happened to me when I did a merge and one of the join columns had a row with an error in it.

I fixed the data so the error wasn't there and the message disappeared

AlexanderPrime
Solution Supplier
Solution Supplier

To add my experience to the ring, this happened to me when an additional column was added to an Excel File Data Source that was connected too via the "Sharepoint Folder" method. Setting up the connection again, deleting the old one and renaming the new connection to match the old one worked and saved having to redo all the DAX. 


Please note - I am not affiliated with Microsoft, I'm just an end user like yourself.
Just a regular guy doin' Data Science.

If my post has helped you, please don't forget to thumbs up or click "Accept as solution" if it solved your problem!
SimonAurizon
Advocate I
Advocate I

When I faced this error, the issue turned out to be that my query was using a 'Helper Query' involving a 'Sample File' and the sample file no longer existed in the original location.

 

The query was using 'Source = SharePoint.Files' and I had relocated the SharePoint folder. While had updated the filepath in my query itself, I'd neglected to realise that Power BI Desktop had created a 'Helper Query' and that I needed to update the source in that too.

I have followed your steps below and resolved my issue:

1) I had relocated the SharePoint folder and 

2) updated the filepath in my query

3) but neglected to realise that Power BI Desktop had created a 'Helper Query' and that I needed to update the source in that too

Thank you Simon

Mrab
Helper I
Helper I

I just want to let you know how this error occurred in mine and how I solved it. 

 

A business process that was used in my Power BI:

I was pulling data from a particular folder and the folder had one file which was updated after a unit time. One day this error showed up.

 

Solution:

The problem was that there was no file in the folder and this caused this error to occur. the empty folder was causing this to fail.

 

I hope this helps in any way.

I had this error after moving the folder where i kept files to a different location and forget to change new location in one of the table.

can you share a screenshot of the column names for the 2 files or more than 2 files you are using?

Anonymous
Not applicable

The error message could also be caused by referencing non-existent column names.

 

For example, "Schedule 2 PMT Amount" was renamed to "PS28_AMT_2" through the Advanced Editor.

If Schedule 2 PMT Amount was referenced in subsequent steps, the column will not be recognized.

 

image.png

I am getting this same error. The curious thing is that I haven't updated the data or the queries at all. Yesterday things were fine. This morning I am getting errors as soon as I try to open the file.  Maybe it's just a bug.

v-sihou-msft
Employee
Employee

@sebbyp

 

This error is thrown when pivoting columns with same name. Please check your column names in your data source. Also refer to a similar thread below:

 

https://social.technet.microsoft.com/Forums/en-US/7923e025-a0c4-407b-bd0f-65959e5038bb/what-does-thi...

 

Regards,

This error is also caused when merging data from another table that has duplicates.

To add to this, here's a video tutorial by @ruthpozuelo that I was very grateful to have found:

How to fix the "Too many elements in the enumeration to complete..." pivot error in Power Query

Curbal, Mar 16, 2020

https://www.youtube.com/watch?v=aTXQ77Vf5Gc 

@sebbyp 

I couldn't find a duplicate column.  

 

From Power Query Editor
= #"Reordered Columns"{0}[Planned Date]

Expression.Error: There weren't enough elements in the enumeration to complete the operation.
Details:
[Table]

This soultion was helpful in that it gave met ideas on how to find ther error, but I found the error in my query was not related to pivoting but referring to a row at an index that did not exist. I'm including an explanation here in case it helps others:

 

I was adding a column that would store a list of values for the current record, but I had initially created an index that started at 1 instread of 0. Therefore, the last record in my query table would have an error value. Since this was outside of the preview range, the error was not apparent until I tried to load to the data model. Lesson learned: the first record in a table should be referred to with a zero, i.e. TableName{0}

 

#"Record Vlaues" = Table.AddColumn(#"Added Index", "NewColumn", each Record.FieldValues(#"Added Index"{[Index]}))

I know this is an old post.  However I'm having the same issue right now.  The strange thing is if I refresh several times then it eventually works.  Sometimes it works on the 1st try.  This is while not changing anything.  I'm rather new to this as most of this was built for me, but I'm trying to learn it and I just simply can't figure this out.  I've check all the columns and such, but nothing is jumping out at me.

I know this is an old post, however, I'm having the same issue, the strange thing about mine is if I hit refresh several times it will eventually work generally.  Sometimes it will even work on the 1st try.  I'm not very good with this stuff as it was mostly built for me so I'm trying to learn more about it but this error is beyond frustrating.

arify
Employee
Employee

Hi,

 

Can you tell us more about the steps you took? Can you give us the M query? (Query Editor > Advanced Editor)

 

Thanks

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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