In spite of applying these steps, each time the query is updated, each one of them processes all the records of the database, that is to say, the 2 million. This is too slow, is there any way to decrease the updating time?
First off, are you able to solve this with some sql code for the historic date? Just place a simple filter on the table by date.
This is by far the best method to explore first and one I’ve used previously.
Seems odd that it is querying it twice though even with the adjustments you’ve made to the load and report refresh.
I just read that article you linked though and it’s answers this question as well
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!:
If SQL isn’t an option the example you’ve included is a good one by using UNION.
All your doing to append inside Power BI rather than the query editor.
So don’t do the append like you are doing right now.
Just load the two table into the model and then create the one table with the UNION function. You can do this with the table function feature.
It’s not ideal though as you will have double the amount of data in your model, that’s why initially I suggested the SQL option. That would work slightly better in my opinion.
