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, 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.
Hi yoa380 , Thank you for reaching out to the Microsoft Community Forum.
Your issue arises because Power BI Desktop only references pre-published data in dependent queries, even after IR refreshes the full dataset. To ensure that your pivot query references the fully refreshed partitions, the best solution is to use DirectQuery over the published dataset, as it dynamically queries the latest refreshed data. Alternatively, you can use a Dataflow to manage the extraction and pivot, ensuring it references the fully refreshed dataset.
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 : if I understand correctly, do you suggest to first create a dataflow in my workspace ? But I was told we can't use IR over a dataflow. Maybe not ? and if yes, what kind of dataflow 1 or gen2? Also, is it possible to create a dataflow when sources are csv/txt files on a Sharepoint?
Then, if that's correct, my model will take this dataflow as source and I'll be able to have queries with pivot ?