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, 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.
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 AdvanceHello, I am still struggling. First online refresh keeps on telling me a column by naming it is missing in a table. I have a column with this name in many tables. I don't have any table named the same as this column.
And the refresh works very well in Desktop BI without any issue.
"Expression.Error: The column 'Gap vs min stk objective' of the table wasn't found.. Gap vs min stk objective. . The exception was raised by the IDbCommand interface."
To recap : I have in my sharepoint files with dates from 1 march 24, 21 nov 24 then every week day from 17 dec 24 to 7 march 25.
My parameteres are RangeStart = 1 March 24 and RangeEnd = 7 march 25 (but I read these parameters are ok with any values as Online BI will do its logic) They are Date Time format.
My desktop model has refreshed successfully (with loading files from 1 march 24 to 6 march 25 (not the 7 march 25) as RangeStart is >= and RangeEnd is <
The exact formula is : Table.SelectRows(#"Type modifié4", each [Date File] >= Date.From(RangeStart) and [Date File] < Date.From(RangeEnd))
The date column used is in Date Format not Date Time (this is why I used Date.From)
After this step in Query, it's directly the automatic fonction to merge file data starting by Hidden Files Filtered etc.... as usual.
This table that call Sharepoint files has the IR setup like this : Archive 12 Months (telling me from 1 March 24 to 6 March 25 (inclusive)) and Refresh 1 Day (telling me from 7 march 25 to 7 march 25 (inclusive)).
NO other options is ticked.
Do you have an idea ?
