Forum Discussion
Impactful Individualone step in setting up incremental sm's is confusing
hi i think i mostly understand these AI steps for incremental SM update. But what is that windows step about? Am i setting something in the OS on my pc that makes the range start and range end dates slide, and is that something going to be somehow plumbed to my report dynamically in the service?
Hi db042190,
I think the missing piece is that the two windows are really about how Power BI manages date-based partitions, rather than Power BI checking each individual record and deciding whether to insert or update it.
For example, with Store 3 years / Refresh 10 days, the initial service refresh builds the historical data covered by that policy. On subsequent refreshes, Power BI refreshes the partitions covering the most recent 10 days, while the older partitions remain stored in the model.
Microsoft explains this rolling-window behaviour in the incremental refresh overview.
The date column filtered by RangeStart and RangeEnd is therefore mainly used to determine which partition a row belongs to. Power BI is not doing an "insert if this customer ID doesn't already exist" check against each individual row.
If records can be modified later, there is also a separate optional Detect data changes setting. That can use something like a LastModifiedDate or audit datetime column, and Microsoft recommends that this be different from the column used with RangeStart and RangeEnd.
The incremental refresh configuration documentation explains that distinction.
For your customer-table example, this matters because if you partition by CreatedDate, but a customer created two years ago can still be changed today, a 10-day refresh window would not automatically bring that old partition back into the normal refresh window simply because the customer was modified.
Microsoft also recommends not changing the date used to partition a record after the fact, because moving records between partitions can lead to duplicate or relationship issues. That behaviour is covered in the incremental refresh troubleshooting guidance.
So if this is a relatively small customer or dimension table where historical records can change regularly, I would also consider whether a normal full refresh is simpler. Incremental refresh tends to be most useful on large tables where most historical rows become stable over time.
AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
11 Replies
db042190i followed the windows step link and it seems a little misaligned with the overview. i'm missing something.
- tayloramy
Super User
Hi db042190,
AI, especially the google search AI, very commonly gets things wrong and makes things up.
I'd recommend using microsoft documentation as your source of truth for how to do things:https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-configure
- db042190
thx amy, i guess that AI is called Gemini. The MS document is confusing. And it has a link to something "i'd better understand" even though i wanted to understand it in the first link. sarcasm aside, its unclear what it will do for us after the first publication. the param settings are pretty clear and the concept of partitions seems reasonable to someone who has worked with changing dims forever. and if i read the doc correctly the 2 date params were set about a year apart. but i got lost in the policy setting and what happens after 1st publish. i'm just trying to get a lay of the land so i can determine if this is something we want to get involved with. Is MS basically telling me that if a change (eg address line 1) to a customer record occurred for the 1st time in forever, i'd better have a field in the cust record that contains a change date/time? and even so , how will pbi know not to insert a duplicate in the sm's customer table?
- tayloramy
Hi db042190,
In general, to be able to do incremental loading, there does need to be a field that contains the datetime the record was last updated. Then Power BI will pull all records where that field is newer than the last time Power BI successfully refreshed.
This is not just how Power BI works, but how incremental data loading works in general when tables contain updates.
Now if your table is append only, then you can get away with an auto incrementing int column, and Power BI will load all records with an ID higher than the highest ID found last load, but if there are updates involved this falls apart.
v-kathullacCommunity Support
Hi db042190
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
Regards,
Chaithanya
- db042190
reading the two most recent replies from amy and shivekmaharaj now. bear with me.
- ShivekMaharaj
Community Champion
Hi db042190,
I think the missing piece is that the two windows are really about how Power BI manages date-based partitions, rather than Power BI checking each individual record and deciding whether to insert or update it.
For example, with Store 3 years / Refresh 10 days, the initial service refresh builds the historical data covered by that policy. On subsequent refreshes, Power BI refreshes the partitions covering the most recent 10 days, while the older partitions remain stored in the model.
Microsoft explains this rolling-window behaviour in the incremental refresh overview.
The date column filtered by RangeStart and RangeEnd is therefore mainly used to determine which partition a row belongs to. Power BI is not doing an "insert if this customer ID doesn't already exist" check against each individual row.
If records can be modified later, there is also a separate optional Detect data changes setting. That can use something like a LastModifiedDate or audit datetime column, and Microsoft recommends that this be different from the column used with RangeStart and RangeEnd.
The incremental refresh configuration documentation explains that distinction.
For your customer-table example, this matters because if you partition by CreatedDate, but a customer created two years ago can still be changed today, a 10-day refresh window would not automatically bring that old partition back into the normal refresh window simply because the customer was modified.
Microsoft also recommends not changing the date used to partition a record after the fact, because moving records between partitions can lead to duplicate or relationship issues. That behaviour is covered in the incremental refresh troubleshooting guidance.
So if this is a relatively small customer or dimension table where historical records can change regularly, I would also consider whether a normal full refresh is simpler. Incremental refresh tends to be most useful on large tables where most historical rows become stable over time.
AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
- db042190
thx shivekmahareaj, i think i can see this feature being useful on facts like sales trans. we often go back 7 days knowing we can easily pick up records we've seen before but this mitigates erp backward and forward dating and our etl knows how to deal with it.
