Forum Discussion
What is wrong with my incremental refresh?
I have a query that reads and processes excel files on SharePoint. (It gets file using SharePoint.Contents.) I am trying to implement incremental refresh and, at the same time, not read every file on the first refresh.
RangeStart & RangeEnd & Filter were auto-created by service during configuration. The resulting query looks something like this:
let
Source = SharePoint.Contents( "https://myco.sharepoint.com/teams/mysite/", [ApiVersion = 15] ){[ Name = "mylib" ]}[Content],
#"Actual work" = do stuff here,
#"Header Info-44617465206D6F646966696564-autogenerated_for_incremental_refresh" = Table.SelectRows(#"Actual work", each DateTime.From([#"Date modified"]) >= RangeStart and DateTime.From([#"Date modified"]) < RangeEnd)
in
#"Header Info-44617465206D6F646966696564-autogenerated_for_incremental_refresh"
After the dataflow runs, I am inspecting it in Power BI Desktop.
I have run with the filter prior to the "Actual work" so that it is folded, but the following anomaly happens whether filter is near the beginning or at the end of query:
run1: set "Store rows from the past" to 12 months - 462K rows returned from 415 files
run2: set "Store rows from the past" to 18 months - 378K rows returned from 343 files
It should go without saying that past 18 months encompasses more files and rows than past 12.
Thb, this incremental load is not going to work here in a proper (designed) way. In the past is was not possible to have one with this data source. This might have changed now?
So if the amount of data from the Sharepoint is too big maybe this might be not the best way to use Sharepoint as a "Database". You are still reading files...this is something completely different like reading from a db. Using Sharepoint like this is often a pain.
If you can try another (more robust) approach.
Another idea...
Maybe you can split your data in different dataflows - each with 6 months of data or so. Maybe you don't need data older than 6 months?
Merge the results afterwards.
Hope you got the idea.
Regards
9 Replies
Hi hansei,
This issue happen when you apply the date filter after Actual work , Power Query still processes all files from SharePoint during the Actual work step then filters the results afterward☺️❤️
This means:
All 415+ files are being read and processed every time
The filter only reduces the output (not the workload)
You are paying the full performance cost regardless of your date range
So what to do Now to solve this?
You need to push the filter upstream to the SharePoint.Contents level
let Source = SharePoint.Contents("https://myco.sharepoint.com/teams/mysite/", [ApiVersion = 15]), mylib = Source{[Name="mylib"]}[Content], // Apply date filter IMMEDIATELY after getting the file list FilteredFiles = Table.SelectRows(mylib, each DateTime.From([#"Date modified"]) >= RangeStart and DateTime.From([#"Date modified"]) < RangeEnd), // Then do your actual work on only the filtered files #"Actual work" = ... process FilteredFiles here ... in #"Actual work"So to make it success you should:
- Apply date filters as early as possible in your query
- Ensure the filter translates to native API calls (SharePoint filtering)
- In Power BI Desktop check View Native Query to see if folding is occurring
So if you want to test it First before go deep (Temporary Testing)
- Add a counter to see how many files are being processed:
FilteredFiles = Table.SelectRows(mylib, each DateTime.From([#"Date modified"]) >= RangeStart and DateTime.From([#"Date modified"]) < RangeEnd), FileCount = Table.RowCount(FilteredFiles),This should show significantly fewer files when using smaller date ranges
- A properly config incremental refresh should get faster as the date range narrows
if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.My question is not about performance or optimization. My question is why the amount of data stored for a larger date range is smaller than the amount of data stored for a smaller date range. i.e. why is rowcount reduced?
Hi hansei,
Sorry😅❤️
so this issue occurred cause :
- Time zone mismatch (RangeStart/RangeEnd (UTC)) vs your file dates (local time)
- Data changing between refreshes (files being moved/deleted/modified)
Filter logic error especially with date comparisons and DateTime.From() conversions
So you add this diagnostic step right before your filter for checking:
// Check what is actually happening Diagnostic = Table.AddColumn(#"Actual work", "DateCheck", each [ RawDate = [#"Date modified"], ConvertedDate = DateTime.From([#"Date modified"]), InRange = DateTime.From([#"Date modified"]) >= RangeStart and DateTime.From([#"Date modified"]) < RangeEnd ])This will show if dates are converting/compairing correctly.
Resources :
I hope this was useful ☺️❤️
Hi hansei
Have you had a chance to look through the responses shared earlier? If anything is still unclear, we’ll be happy to provide additional support.Hi hansei
Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we’ll assist further.
