Keeping existing data in data model and just extend it with new data

Question

What I do:
I built ETL processes with power query to load data (production machine stop history) from multiple Excel files directly into PowerBI. On each new shift (every 8 hrs.) there is a new excel file generated by the production machine that need to be loaded to the data model too.

How I did it:
To do so, power query is processing all files found in a specific folder.

The problem:
During query refresh it need to process all the data files again and again (old files + new files).
If I remove the old files from the folder, power query removes the data also from the data model during the next refresh cycle.

What I need / My question:
A batch process copies new files into the folder while removing all the old files.
Is there a possibility to configure powery query in a way that it keeps the existing data inside the data model and just extend it with the data from the new files?

What I would like to avoid:
I know building a database would be one solution but this requires a second system with new ETL process. But power query does already a very good job for preprocessing the data! Therefore and if possible, it would be highly appreciated if this problem could be solved directly inside power query / power bi.

Andrey Nikolov · Accepted Answer · 2019-10-07 11:06:23Z

0

If you want to shoot sparrows with a cannon gun, you could try incremental refresh, but it's Premium feature.

In Power BI refreshing a dataset reloads it, so first it is cleared, and second - you will need all the files to re-load them and recalculate everything. If you don't want this, you have to either change your ETL to store the data outside of the report's dataset (e.g. a database would be a very good choice), or to push the data from the new files only to a dataset (which I wouldn't recommend in your case).

To summarize - the best solution is to build ETL process and put the data in a datawarehouse, and then to use it as a datasource for your reports.

answered Oct 7, 2019 at 11:06

Andrey Nikolov

13.5k3 gold badges26 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Keeping existing data in data model and just extend it with new data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related