Forum Discussion
Incremental Refresh - issue
Hello!
I have an issue with the incremental refresh. I followed different YouTube videos where it worked but everytime I publish the model on my Online Space I get a refresh error message : Expression.Error: There weren't enough elements in the enumeration to complete the operation.. #table({"Content", "Name", "Date"}, {}). ;There weren't enough elements in the enumeration to complete the operation.. The exception was raised by the IDbCommand interface.
The workspace is Premium and has a diamond logo.
Here is how I built my test file in Desktop :
I call files from a Sharepoint using Sharepoint.Files. I filter down to the test folder I want. My files names have the date (there are daily files). I use their names to get a new column giving me their date. I put this column in Date/Time format.
I create 2 parameters (RangeStart and RangeEnd), both having the mandatory box activated, Date/Time format, middle box is "ALL" and Current Value is 24/02/2025 00:00:00 for Start and 01/03/2025 00:00:00 for End.
Back in the main query : I filter my dateTime column with :
= Table.SelectRows(#"Type modifié", each [Date] >= DateTime.From(RangeStart) and [Date] < DateTime.From(RangeEnd))
Then, it's directly the Transformation automated steps that load the files contents. (I have deleted the automated step saying something like Hide columns, but it doesn't matter as the issue was here before and after I did that).
This is working well in Desktop, I save & load.
I click right on the table and create incremental refresh parameters : I select my table (I have only one), activate the button, Start of data to archive is set for 2 years before refresh date & start of refresh is set with 2 days before refresh date. The schema created seems logic for me. No other box is selected below that.
I load my model online and click on refresh button. After 10 min I always get the error message I gave at the begginning of my post.
I have no more ideas on other parameters or things I forgot to do or did wrong ...
Hi yoa380 , Thank you for reaching out to the Microsoft Community Forum.
The issue you're encountering happens because SharePoint.Files() does not support query folding, which is necessary for Incremental Refresh to function in Power BI Service. As your query doesn't fold, Power BI retrieves all the data before applying the filtering logic, leading to the refresh failure.
There are two possible solutions for this:
- In Power Query Editor, replace SharePoint.Files("https://yourtenant.sharepoint.com/sites/yoursite") with SharePoint.Contents("https://yourtenant.sharepoint.com/sites/yoursite"). Navigate directly to the folder containing your files. Apply the RangeStart and RangeEnd filters. This approach folds the query, allowing Power BI to push filtering logic to the source, making Incremental refresh work properly.
- If SharePoint.Contents() still doesn’t fold, create a Dataflow in Power BI Service, Connect the dataflow to SharePoint and apply transformations, Enable Incremental Refresh on the dataflow. In Power BI Desktop, connect to the dataflow instead of SharePoint directly.
If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.
16 Replies
I think I found something but would be good to have confirmation. My archive setup was = 2 years but in my sharepoint test folder I only had 4 files with 4 dates in the past for this week. If I change incremental setup to archive = 4 days, it works Online. I think he needs to find at least a file in Sharepoint corresponding to the beginning of my archive date & only for the first big refresh Online. Then, once done, next refreshes can work even if I delete those files from Sharepoint. My online report still keep old data.
Hi yoa380 I think your analysis is correct , The issue occurs because Power BI's initial refresh requires data for the entire archive period. If your SharePoint folder only has recent files, the refresh fails as there isn’t enough data to populate the archive.You could try these as workaround
Shorten the Archive Period Temporarily:
- Set the archive period to match the available files.
- Publish and perform the initial refresh successfully.
- After the first refresh, revert to the 2-year archive period if needed.
Add Dummy Files:
- Add dummy files in SharePoint to cover the 2-year archive.
- Perform the initial refresh, then delete the dummy files.
Thank you for your confirmation ! just a question, for workaround 1 : once model uploaded online, how do you change incremental period ? (you don't necessarly have to set it up in the model on Desktop and iupload again ? do we have the incremental setup also online once uploaded at least once ?
Hi yoa380 , Thank you for reaching out to the Microsoft Community Forum.
Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.After watching few more videos and pages, I found another difference with my model. My filter query has "native query" greyed out. Maybe Sharepoint.Files kind of source cannot work with Incremental Refresh .... ?
Hi yoa380 , Thank you for reaching out to the Microsoft Community Forum.
The issue you're encountering happens because SharePoint.Files() does not support query folding, which is necessary for Incremental Refresh to function in Power BI Service. As your query doesn't fold, Power BI retrieves all the data before applying the filtering logic, leading to the refresh failure.
There are two possible solutions for this:
- In Power Query Editor, replace SharePoint.Files("https://yourtenant.sharepoint.com/sites/yoursite") with SharePoint.Contents("https://yourtenant.sharepoint.com/sites/yoursite"). Navigate directly to the folder containing your files. Apply the RangeStart and RangeEnd filters. This approach folds the query, allowing Power BI to push filtering logic to the source, making Incremental refresh work properly.
- If SharePoint.Contents() still doesn’t fold, create a Dataflow in Power BI Service, Connect the dataflow to SharePoint and apply transformations, Enable Incremental Refresh on the dataflow. In Power BI Desktop, connect to the dataflow instead of SharePoint directly.
If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.Hello, I still have one issue. So I have now one request in my model that is consolidating the Sharepoint files. This one contains the start/end parameters. This one also holds the IR rules. I also deleted the pivot step because after testing, I realized doing a pivot (putting a column data into 2 new columns) is crashing online refresh. It tells me the new columns name cannnot be found.
Online first refresh works and creates well partitions - taking all files from 1 March 24 to Today.
Even if we have the orange warning that cannot be folded.
But in my model, I have a request that takes Consolidated Request as starting point and doing the pivot. My issue is that this one does not contain all the files data previously loaded with IR.
It only contains the files data I previously loaded in desktop before uploading Online.
I don't have the issue on Desktop, whatever we load in Consolidated initial Request will be available in all other requests that follow and take it as starting point.
