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...
Show and Tell
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

      • automation's avatar
        automation
        Frequent Visitor
        5 months ago

        Hi Natarajan_M ,

        I've implemented a last 60 months incremental refresh with data detect changes on my dataset. However, with Power BI Premium Per User (PPU) license, the dataset is not refreshing completely and I'm encountering timeout errors.

        Could you please help me resolve this issue?

        Thanks!