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

[alexander starbuck]

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

 

[Chihiro]

There are multitudes of method that can be utilized. Depending on your need and comfort level with tools available.

Most of the time, I have dedicated server available, which is utilized as staging server for reporting data. Files are stored in dedicated folder(s) and fetched by stored procedure or by other application into database and gateway server is used to connect to that server from outside our network.

However, this takes some investment and may not be feasible.

Here's some of more low cost methods.

1. File can be set up to be sent to email address with specific subject line (i.e. currentweekdata.csv). Then use MS Flow to create workflow to fetch the attachment from email and save to OneDrive, using subject line as file name. Doing this will overwrite old file with new one each and every time. You'll then connect to the file and data will refresh.

This method is suitable, if you don't need to keep historical data, and data only updates on daily basis.

2. As above set up email and workflow, but use file name that is unique. Then connect to OneDrive folder instead of file. Merge all files within the folder (ensuring all files have same headers etc) using PowerQuery.

This method is suitable, if you need to keep historical data, and when data update is infrequent.

3. Instead of OneDrive & email, use SharePoint list. Concept remains same.

If more frequent update of info is needed consider streaming data using one of following method.

1. Push dataset
2. Streaming dataset
3. PubNub streaming dataset

Have a read of below link.
https://docs.microsoft.com/en-us/power-bi/service-real-time-streaming

Also, if it's Google analytics that you need to pull data from, PowerBI is able to connect to it directly without need to export files.

NOTE: This pertains to PowerBI, which is separate from PowerQuery/Get & Transform. While many of the features are shared, PowerBI is specifically meant to publish report to secure cloud for remote access etc.

 

[alexander starbuck]

Hello Chihiro!

Many thanks for an elaborate and educational reply, much appreciated!

I may have not used proper wording for my problem - I was more concerned about updating the data in the data model (within Excel) from an external file that I will procure manually or through the scheduled email service.

If I connect to a source file as in my example, and the data model loads it all, what will happen next time, when I overwrite this file with another which has more rows and "totals" rows move further down?

what will happen to my pivots "built" on top of this data model? I may have just given myself an answer - test and see!

Thanks once more, I really appreciate your reply and I will try and learn more on how you go about doing this.

 

[Chihiro]

Ok, if you overwrite source file with new file. Basically, everything within query will be replaced with new data (be it less data or more data). Your model will then be filled with this new data and update accordingly.

If a measure is dependent upon some value being present in the model and it is no longer there... it will break the measure. Otherwise, basic model structure will not be impacted.