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.
Hello, thanks for your answer.
So my model is that I have files created in Sharepoint every morning between Monday and Friday (not the weekend). If I create my IR rules on my table with Archive = 12 Months & Refresh = 1 day it will tell me : Archive from 01 March 24 to 6 March 25 (inclusive) and Refresh from 7 March 25 to 7 March 25 (inclusive) Do you confirm that for the first refresh online, I only need to make sure I have a dummy file corresponding to 01 March 24 in Sharepoint ?
When the archive period will slide tomorrow by 1 month in April, my 1 Mar 24 dummy data will disappear automaticcally. Any issue if then the oldest file is not 1 April 24 but something in Nov 24 ? (I mean no need anymore file with date = starting archive period / just taking the files including in the period) ?
Hi yoa380 Yes, you are correct. For the first online refresh, ensure a dummy file exists for the earliest archive date, i.e., January 1, 2024. Once the archive period slides, the dummy file will be excluded automatically. It is not mandatory for the oldest file to match the starting archive period (January 2, 2024, is fine if November 2024 files are present). The system considers files within the defined period only, so this setup will work seamlessly.
For further clarification / Information please refer this : Incremental refresh and real-time data for semantic models.
If this psot helped please do give a kudos and accept this as a solution
Thanks In Advance