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 , 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 ?