Forum Discussion

automation's avatar
automation
Frequent Visitor
5 months ago

Help needed: Detect Data Changes on Modified column with Incremental Refresh

Hi,
I’ve configured incremental refresh on a Power BI dataset.

  • The source is SQL views, and the underlying tables contain both Created and Modified date columns.

  • In Power Query, I applied the incremental range filters using the Created date (RangeStart and RangeEnd).

  • In the incremental refresh settings, I configured it to store 5 years and refresh the last 2 days, and I enabled “Detect data changes” based on the Modified date column.

     

The issue:
When a record from 2021 gets its Modified date updated, the change is not picked up by the incremental refresh, and the updated data does not appear in the dataset.

Can someone explain why this is happening and how I should correctly configure incremental refresh + detect data changes so that updates to older records (e.g., from 2021/2022 etc) are captured?

Regards,
Adeel Nazir

Show and Tell

10 Replies

  • Natarajan_M's avatar
    Natarajan_M
    Super User
    5 months ago

    Hi, automation 

    You are correctly configuring the incremental refresh by using the created datetime to set the RangeStart and RangeEnd, and using the modified datetime to detect changes.

    Can you confirm whether the Modified field is in datetime format?
    Check in your Power Query step whether that row is getting filtered unintentionally 


    Additionally, I recommend configuring the refresh by months instead of years. This way, if a row is updated, the process will only refresh the data for that particular month rather than the entire year. This approach helps isolate the refresh and improves efficiency.

    reference video : https://www.youtube.com/watch?v=JsJWBr1_ktQ

    Thanks 
    If this response was helpful in any way, I’d gladly accept a kudo.
    Please mark it as the correct solution. It helps other community members find their way faster

    • automation's avatar
      automation
      Frequent Visitor
      5 months ago

      Hi Natarajan_M ,

      Thank you for your detailed response to my question I really appreciate it.

      I’ve confirmed that the Modified date is stored as a DATETIME field.

      Questions:

      1. I'm currently configured for "last 2 days data refresh" while keeping 5 years of historical data.
        If I change the historical data range to 60 months, should I also adjust the "last 2 days refresh" value accordingly?

      2. As shown in the screenshots attached to my original post, does the "last 2 days refresh only" setting explain why 2021 records with updated Modified dates are not being picked up by Detect Data Changes?

      • Natarajan_M's avatar
        Natarajan_M
        Super User
        5 months ago

        Hi automation , The "Detect Data Changes" feature operates within the refresh context window. It retains historical data for up to 60 months and allows for incremental data refresh for the same duration. With this setting, all your partitions fall within the "Detect Data Changes" window, enabling the system to identify changes in the data. Consequently, not all partitions will be processed until a change occurs within those partitions.



        thanks ,
        If this response was helpful in any way, I’d gladly accept a kudo.
        Please mark it as the correct solution. It helps other community members find their way faster

  • Zanqueta's avatar
    Zanqueta
    Super User
    5 months ago

    Hi automation,

     

    When using Detect Data Changes, only the incremental refresh period is eligible for refresh. Rows outside this period will not be reprocessed even if their change detection column is updated.
     
    Partition the dataset using ModifiedDate (rather than CreatedDate) so that updated records fall within the incremental range and are reprocessed automatically. 
     
    Use ModifiedDate as the column defining RangeStart and RangeEnd. This is the only configuration that reliably captures updates to older data.
     

    Reference: Configure incremental refresh and real-time data for Power BI semantic models - Power BI | Microsoft Learn

  • automation's avatar
    automation
    Frequent Visitor
    5 months ago

    Hi All,

    I need some assistance with this issue. Can someone please help me and find a resolution?
    Regards,
    Adeel Nazir

  • v-prasare's avatar
    v-prasare
    Community Support
    5 months ago

    Hi automation,
    We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

     

     

    Thank you for your patience and look forward to hearing from you.
    Best Regards,
    Prashanth Are
    MS Fabric community support

  • v-prasare's avatar
    v-prasare
    Community Support
    4 months ago

    Hi @automation,
    We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

     

     

    Thank you for your patience and look forward to hearing from you.
    Best Regards,
    Prashanth Are
    MS Fabric community support