Forum Discussion

hansei's avatar
hansei
Helper V
9 months ago
Solved

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 ...
Need Help
  • sergej_og's avatar
    sergej_og
    9 months ago

    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

Ahmed-Elfeel's avatar
Ahmed-Elfeel
Super User
9 months ago

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.
hansei's avatar
hansei
Helper V
9 months ago

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?

  • Ahmed-Elfeel's avatar
    Ahmed-Elfeel
    Super User
    9 months ago

    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 ☺️❤️

    • hansei's avatar
      hansei
      Helper V
      9 months ago

      The only change is incremental refresh setting "Store rows from the past".

       

       

      Date conversion, time zones, etc. are all consistent between the 2 runs. i.e. regardless of any conversions, it is the same conversion on the 2 runs.

       

      It is not possible for an item to have a modification datetime that is in the past 12 months, but not in the last 18 months.

      Nor is it possible for an item's modification date to be < 12 months on run 1, but then be > 18 months on run 2.

      • v-aatheeque's avatar
        v-aatheeque
        Community Support
        9 months ago

        Hi hansei 


        Based on the screenshot that the only change made is in the Store rows from the past setting (from 12 months to 18 months), while everything else including the DateTime column and conversions remains the same.

        However, when you modify the incremental refresh window in Power BI, the underlying partitions that store data aren’t automatically rebuilt. As a result, the service may continue using cached partitions from the earlier refresh policy (in your case, the 12-month window).

         

        This can lead to unexpected results, such as missing or inconsistent rows between the two runs, even though the data itself hasn’t changed.

        To ensure the new 18-month policy takes full effect:

        • In Power BI Desktop, turn Incremental refresh Off, apply changes, and then turn it On again with the updated 18-month window.
        • Re-publish the dataset to the service.
        • In the Power BI service, run a Full refresh once, this forces Power BI to recreate all partitions according to the new refresh policy.

        After that, incremental refresh will continue normally and include the full 18-month range as expected.

         

        Reference : Using incremental refresh with dataflows - Power Query | Microsoft Learn

         

        Hope this helps !!

        Thank You.