PowerQuery (or PowerPivot): Updated Rows Only?

Question

I'm trying to explore PowerPivot, and as far as I can tell it always wants to work with local data.

If the data-download were a one-time hit, then I could work with that - but whenever I want to refresh it appears to go and refetch all the data again.

Is there any way to use PowerBI (or the underlying PowerQuery) so that it can fetch only new or modified rows and add them to it's current dataset? For example, would a OData feed behave this way?

The backend DB in my case will be MSSQL or SSAS. I control the DB and could add change-tracking columns etc...if need be.

Answer 1

You could conceivably load in the bulk of your data up to a point in time and use that as a table named Static which you then append another table named Dynamic which only fetches rows that you specify (using a filter on a change-tracking column) that are added after that point in time.

The problem with this is that nothing from the Dynamic table ever makes its way into the Static table and you end up reloading stuff unnecessarily.

It's probably possible to implement a solution to pass rows into the Static table, but at that point, you're basically using the PowerPivot data model as a database, which probably isn't best practice.