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

[sebbyp]

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

[Ash2022]

I know this is old, but my finding could help someone else with similar situation. 

I was getting the same error, I found that if you set Keep Errors for previous steps for all the columns, you can isolate the issue. in my situation, I was getting the error because some columns values were with error, so some steps might not work correctly if there are error values in the previous steps. 

[stefancubes]

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]

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]

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]

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]

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. 

[SimonAurizon]

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.

[JannaChadji]

Hi Simon,

Would you delete the query if you realized that it was pointing to folder and details that are no longer used?

[JSwamina]

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]

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.

[tenm677]

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.

[Mrab]

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

[Anonymous]

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.

[SHDJason]

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 · Accepted solution]

@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,

[jnnvac]

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

[kymramosrpo]

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 

[LeoFlo]

@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]

[CR303]

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]}))

[Seaweed81]

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.