How to Append Data to a Master File

We like master data files very much since it contains all the information we need with sufficient historical data for our analysis. In order to have a master file up to date, we have to add/update the information into the file consistently. How do we automate the actions?

Let us assume your updated information contains in a data table named “tbl_A”. Convert the historical data file into a table and name it as “tbl_H”.

Create a query to bring tbl_H in as a connection with power query.

Create another query from tbl_A. Append it with tbl_H. Remove duplications against the primer key column(s). Then load to a new table. Name the table as “Data”

Edit the first query in Advanced Editor. Change the source from “tbl_H” to “Data” manually. Keep the change and back to data sheet.

Now you can get rid of the original historical data file “tbl_H”. Table “Data” will be the updated dataset.

If you have a new update data file like enclosed, you will have your “Data” being updated by clicking the “Refresh All” commend.