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 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
Hello, 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 ?
Hi yoa380 , Thank you for reaching out to the Microsoft Community Forum.
Please try below:
- Check the earliest file that falls within the incremental refresh window. If this file or any file in the period does not contain "Gap vs min stk objective", the refresh will fail. Modify the files manually to include the missing column, even if it's empty.
- If modifying the files is not an option, Power Query should be updated to handle missing columns gracefully. Example:
= Table.AddColumn(Source, "Gap vs min stk objective", each try [Gap vs min stk objective] otherwise null)
This ensures that even if a file does not have the column, the query still runs without errors.
- Sometimes, Power BI automatically removes certain files during the "Combine Files" step. double-check this step and ensure that all expected files are included.
- If needed, upload only a few files with a known correct schema and run the refresh online. This will confirm whether the issue is with the files or something else.
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.