Question about Incremental Refresh

[Anonymous]

Here is our scenario: 

We have data in our CRM system.   Things like sales opportunities, amount, probability of closing the deal, etc.   

Normally, these records are overwritten in the CRM system.   So an opportunity that was at 30% probability today, if it gets changed to 80% tomorrow we lose the view of that record  (because our CRM system does not take snapshots or keep the historical changes).  

My "hope" is that incremental refresh could come to the rescue  (yes we have premium).     The idea being that I would publish a range of records (start date, end date) and that would be my historical reference.    Then using incremental refresh, new records (based on the last modified date column) would just be automatically appended to the historical record.  

This would allow us, for example, to compare last week's data to this week's data...and see what changed.  

Is this how incremental refresh works?  

[Greg_Deckler · Accepted solution]

No, that is not how incremental refresh works. Incremental refresh updates records that have changed. It is not an append, it just overwrites records that have changed since the last update. This prevents having to load the entire dataset every time. That is the purpose and function of incremental refresh.

There is no "easy" way to do what you are trying to do. You would basically have to create a new query for every refresh. You would use that query once, mark it to not refresh and then copy and paste it, use that one once, mark it to not refresh, and so on. Then you could create a single master Append query and remove duplicates most likely. As I said, no easy way that I know of...

[Anonymous]

Thank you @Greg_Deckler  and @amitchandak        Not the answer I was hoping for but appreciate the clarity. 

Seems this would be a common need for businesess so perhaps the PBI team can address one day.    Ideally we would have this capability built into our CRM software but we don't seem to have that.  

[Greg_Deckler]

@Anonymous - What CRM are you using?

[Anonymous]

It's called Infor. We use the a cloud version of it and I think version 10 may have snapshots available.   But our current version does not seem to.  

[v-yingjl]

Hi @Anonymous ,

For incremental refresh, datasets are filtered by using Power Query date/time parameters with the reserved, case-sensitive names RangeStart and RangeEnd. These parameters are used to filter the data imported into Power BI Desktop, and also to dynamically partition the data into ranges once published to the Power BI service. The parameter values are substituted by the service to filter for each partition. There's no need to set them in dataset settings in the service. Once published, the parameter values are overridden automatically by the Power BI service.

For more information about how incremental works, the following attached links could also help you to understand:

1. Incremental refresh document 
2. Incremental refresh case 
3. Incremental refresh viedo 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

I doubt that looking at the document. https://radacad.com/all-you-need-to-know-about-the-incremental-refresh-in-power-bi-load-changes-only

You can explore the advance options at https://docs.microsoft.com/en-us/power-bi/service-premium-incremental-refresh

@parry2k , @Greg_Deckler , Can you please help on this