This article describes the latest workaround for incremental load in PBI (thx to Taylor Clark for stressing this out!). It’s not very dynamic, as it doesn’t automatically load the difference to the existing data. Instead you have one query that contains your old data (which will be kept) and another query that grabs all data that comes after the last item from your old data. But at least it’s a technique that works without a hack:
Incremental Load Process
1 Create “Old Data”: “DontRefresh”
So it’s up to you to split up your long table or web-load activities and load your “old” stuff into one query (“DontRefresh”), perform your transformations and then load into the data model once. Then go back to the query-editor and disable the option “Include in Report Refresh”.
2 Create “New Data”: “Refresh”
Then take the cut-off-filter-criterium and use it to define the load of the new data that will subsequently be refreshed (“Refresh”). Transform your transformations from the first query into a function to make sure both tables have the same structure and load it to the data model (leaving the default load options to refresh).
3 Create table using UNION in DAX
In the data model, you create a new table that appends both tables to each other (and hide both input-tables from client view):
Create table in DAX using UNION
Another reason why this is not ideal is that fact that you cannot perform data transformations in the query-editor that iterate over the whole table. So I really hope that incremental load will once be a native functionality in PBI. Please vote for it here, as Microsoft prioritizes many of its activities on customer feedback: I vote for incremental load
Just picked up a useful tip from Mimoune Djouallah, to use a syntax like this:
union (summarize(table_current, field1,field2),summarize(table_history,field1,field2))
Which highlights the difference between the Append-command from M and the Union from DAX: The Union function requires the columns to have the same order in your table.
4 Why not use Append in the query-editor instead?
Another drawback of the current implementation is a somewhat unintuitive behaviour of queries which have been set to “Don’t include in Report Refresh”: As a standalone-query, they will behave as expected and not refresh. But once you reference them by a separate query or within an append-operation, they will refresh their results. So beware of this potential trap!:
Unexpected Behaviour Warning
Link to file:
csv-sample data: https://www.dropbox.com/s/fovsg2xzclfqrzh/ImportData.csv?dl=0
So don’t forget to vote and stay queryious 😉