Refresh Error (Service only)

[lnedeah]

Hi there,

I keep getting this refresh error on Power BI service - Expression.Error: There weren't enough elements in the enumeration to complete the operation.. #table({"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}, {}). ;There weren't enough elements in the enumeration to complete the operation.. The exception was raised by the IDbCommand interface. Table: 22/23 Salaries. The table the error refers to often changes.

When I go into desktop and refresh, everything is working fine. None of the tables are blank. All of the data I am using is from the same sharepoint folder.  I've tried clearing cache and checking the connection. I've even removed the tables and added them back in. I've never had issues with this dashboard before but recently added in a few extra tables/queries, but from the same data source and following the same steps. I can't wrap my head around why the refresh is failing if it's fine in desktop.

Any thoughts?

Thanks!

[v-pnaroju-msft]

Thankyou, @BhavinVyas3003, @Nasif_Azam, for your response.

Hi lnedeah,

We sincerely appreciate your inquiry posted on the Microsoft Fabric Community Forum.

Based on my understanding, the primary cause of the error message, “Expression.Error: There weren’t enough elements in the enumeration…”, arises due to the difference in how Power BI Service processes folder queries compared to Power BI Desktop. Specifically, Power BI Service attempts to process one or more files that return incomplete or empty data structures. This issue may not be evident during refresh in Power BI Desktop because of local caching and differing credential contexts.

The issue tends to occur only in the Service environment due to the following reasons:

1. The Service does not utilize local cache and may encounter hidden, temporary, or unsupported files in the SharePoint folder (such as .tmp, ~$ files, or blank files).
2. Header promotion or transformations applied to empty tables can result in this error during Service refresh.
3. Authentication or privacy level mismatches may prevent certain files from being accessed during the Service refresh.

Kindly follow the steps below which may help in resolving the issue:

1. In Power Query, filter the folder query to include only the required file types (for example, .xlsx or .csv) and exclude system or temporary files (such as ~$ files or empty files).
2. Add error handling and, before promoting headers, use the following expression:
   if Table.IsEmpty(Source) then Source else Table.PromoteHeaders(Source)
3. Verify the credentials in Power BI Service by navigating to Dataset Settings > Data source credentials. Use OAuth2 authentication and ensure that the privacy level is set to “Organizational”.
4. Ensure consistency in the file structure by maintaining identical columns and headers across all files in the folder.
5. Re-publish the report after making the necessary adjustments and reconfigure the credentials accordingly.

Additionally, please refer to the following links for further guidance:
Power Query SharePoint folder connector - Power Query | Microsoft Learn
Data refresh in Power BI - Power BI | Microsoft Learn
Error handling - Power Query | Microsoft Learn

If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members encountering similar issues.

Should you have any further queries, please do not hesitate to contact the Microsoft Fabric community.

Thank you.

[Nasif_Azam]

Hey @lnedeah ,

This error typically occurs when Power BI tries to construct a table with a defined schema (column headers), but the data (rows) doesn't match specifically, it's expecting 8 fields per row and is getting fewer (or none). 

1. Power BI is using a SharePoint Folder connector where schema detection might fail if the sample file is missing or unreadable.

2. The folder may contain empty or invalid files that don't provide all the expected fields.

3. A query may have a hardcoded table structure that doesn’t match the actual data during refresh.

4. Power BI Desktop uses your local credentials, while the Service uses stored credentials that might lack access.

Try 5 Fixes:

1. Review Your SharePoint Folder Query

Go to the query that uses SharePoint Folder, and look for a step like:

= #table({"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}, {})

Make sure it's not trying to create a table with no data. If you see {} there, it's trying to construct a table with no rows.

2. Check Data Source Credentials in Power BI Service

Go to Power BI Service → Settings → Datasets → your dataset → Data source credentials.

• Make sure you're using OAuth 2.0 and the connection is valid.

• Re-authenticate the SharePoint Folder connection if needed.

3. Validate Files in the Folder

Make sure all files in the SharePoint folder you’re referencing:

• Match the expected schema

• Are not empty

• Are not in a temporary or unsupported format

Also, avoid mixing file types (.xlsx, .csv, .tmp) if you're filtering by extension later in the query.

4. Add Defensive Logic in Power Query

Modify the query that uses #table or the folder to only proceed if files are found:

let
    Source = SharePoint.Files("https://yourtenant.sharepoint.com/sites/yourSite", [ApiVersion = 15]),
    FilteredFiles = Table.SelectRows(Source, each [Extension] = ".xlsx"),
    CheckIfEmpty = if Table.IsEmpty(FilteredFiles) then error "No files found" else FilteredFiles
in
    CheckIfEmpty

This avoids passing an empty table into downstream steps, which may trigger that enumeration error.

 

5. Refresh from Power BI Desktop and Re-Publish

If you've fixed anything in Power Query:

1. Refresh in Power BI Desktop to confirm it works.

2. Save and Publish again to Power BI Service.

3. Schedule a refresh or trigger it manually.

For Detailed Information:

Troubleshoot Power BI Data Refresh
Connect to SharePoint folder in Power BI
Configure scheduled refresh - Power BI
Power Query M error messages

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[lnedeah]

Hi, thanks. Yeah I've seen this explanation before but the table isn't empy, and I'm not getting this error in desktop. Do you have any idea why this error would only show in service? Thanks

[Nasif_Azam]

Hey @BhavinVyas3003 ,

You're absolutely right to wonder why something that works fine in Desktop fails in the Power BI Service. That difference is often the key to solving issues like this.

Why It Fails in Power BI Service but Not in Desktop?

When you refresh in Power BI Desktop, the queries run in your local environment using your own permissions and network. But in the Power BI Service, the dataset is refreshed in Microsoft's cloud environment using saved credentials which may behave differently, especially for file access or authentication. Even though the table isn't empty in Desktop, here are a few reasons why the Service might still throw error.

Common Service-Only Issues

1. Power BI Service might detect the file but fail to read its content due to access limitations or temporary file locks.
2. The Service may attempt to read a file before it's fully uploaded or synchronized, leading to incomplete data.
3. Stored credentials in the Service may not have the same access as your local credentials, especially if tokens have expired or scopes are limited.
4. If the files in the folder change or one is malformed, the Service may struggle to apply the expected structure during refresh.

What You Can Do Next

• Add a conditional check using Table.IsEmpty to prevent the query from continuing if no valid files are found.
• Create a simplified test query that loads only basic file details to isolate whether the issue occurs in Service.
• Use Table.Buffer() to avoid inconsistent behavior caused by lazy evaluation when referencing the same step multiple times.
• Go to the Power BI Service dataset settings and re-authenticate the SharePoint connection using OAuth2 to ensure proper access.

For Detailed Information:

Troubleshoot Power BI Data Refresh
Connect to SharePoint folder in Power BI
Configure scheduled refresh - Power BI
Power Query M error messages

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[BhavinVyas3003]

The error indicates that one of your folder queries (e.g., from SharePoint) returned an unexpected or empty structure during refresh in Power BI Service, likely due to a missing or unreadable file, a promoted header step being applied to an empty table, or the presence of unsupported or hidden files in the folder.