Forum Discussion
Incremental Refresh - issue
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.
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.
Yes, you are correct. 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.
Here are two workarounds:
- In Power BI Desktop, set the archive period to match the available files. Publish and complete the initial refresh successfully. After that, you can increase the archive period in Power BI Service without needing to republish.
- Upload temporary files in SharePoint with older dates to match the archive start date. Perform the initial refresh in Power BI Service. Once successful, you can delete the dummy files.
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, thank you for your answer. I have 2 questions :
For workaround 1 : where in Power BI service do you change archive period? Let's say that my archive period is 1 month initially. I will load the model online, perform the first refresh. If I understand correctly, it will load 1 month data + the refresh period. Then if I extand the archive period to like 1 year : the 1 year data won't be here as it only loaded 1 month initially ? And also will it need every single daily files for the period to work ?
Because in my test where I put archive = 7 days for example, it looks like the first refresh only works when in my Sharepoint there are the 7 daily files. If I miss 1 day, it fails. In reality for my real needs, I won't have daily files, sometimes it's weekly. Can it work in some way ?
workaround 2 : let's say I put archive is 1 month but only have 1 week of files. Do I need to just create a dummy file with 1 month ago date ? and it will disapear as the archive periode will slide ? or I also need to create dummy files for each day of the period ?
Hi , Thank you for reaching out to the Microsoft Community Forum.
- Once a dataset with incremental refresh is published, you cannot change the archive period in Power BI Service. Incremental refresh settings must be defined in Power BI Desktop before publishing. If you need to modify the archive period, you must update the settings in Power BI Desktop and republish the dataset.
- No, you do not need a dummy file for every missing day. Power BI only requires at least one file within the oldest archive date range for the first refresh. Once the initial refresh is successful, you can delete the dummy files, and Power BI will continue to retain old data as the archive period slides forward automatically.
- Yes, but you need to ensure that the query logic correctly selects the latest available file within the refresh period. Instead of expecting daily files, adjust the filtering logic so that Power BI can find the most recent file available within each refresh window.
Also, please refer to this document: Incremental refresh and real-time data for semantic models.
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.