[Excel 2016] - Proper way to regularly update data with Power BI (Get & Transform)?

[astrbac]

Hi all!

Recently I learned about the PowerBI features (Excel's data-model, Get&Transform, etc.). This seems like THE thing I need in my regular job, as I keep compiling reports from different data sources and updating them.

My question is:
What is the proper way to regularly update data if using Excel's data model with linked files, instead of imported data from sheets/tables?

For example - each week I pull data from our Google advertising account in .xls or .csv format (example attached here, Google Drive). If I load this into the data model and download another .xls file from Google the following week, how can I make sure that the data model will get properly updated?

Do I simply rename the new file to have the exact same name as the old source file, overwrite the old one? What about the rows in that case?

As I understand, I need to map rows and columns in "Get & Transform" exactly so that the data model knows where to look for the data within the source file. During following updates number of rows will increase, do I need to re-map each time?

IN the example file, data is in rows 4 - 15, in the next weeks file it will be 4 - 30 or even more.

Many thanks for tips and advice!
Alex

 

[RoryA]

Do I simply rename the new file to have the exact same name as the old source file, overwrite the old one?

Yes, that will do the trick.

What about the rows in that case?

Unless you have somehow told it otherwise, the query will extract all the data from the source file.

As I understand, I need to map rows and columns in "Get & Transform" exactly so that the data model knows where to look for the data within the source file.

No, you only need to have the same column headers each time.