Dataset Refresh from Excel File without the manual work?

[irnm8dn]

Help for a Newb...

I have an excel file which I uploaded into Power BI.  After some modest gymnastics with the data (parsing a date field to make a date from text, adding a calcuated column to create an alias, renaming columns and deleting those that are unimportant)...

Voila,  I have the experience I want to make "consumer facing".  

But what about tomorrow? 

Since I can't automate the file, I need to replace it with the "more current" flat file.

I am looking for some instruction how to replace the original dataset (with the new one) without having to recreate all the manipulation I had to do to achieve my desired result.

Please advise how to upload the "new dataset" without going through all the steps I had to to develop the desired experience?

Thanks!

[Phil_Seamark]

You can install a personal gateway on a machine that is always on.  This can be configured to point to your local file and told to refresh several times a day if need.

Have a read here.

https://powerbi.microsoft.com/en-us/documentation/powerbi-personal-gateway/

An alternative might be to store your Excel file up in a One Drive

[irnm8dn]

Thanks Phil, this is great.  Heading in this direction for sure.

But in the meantime, I am looking for some low tech instruction to satisfy a more immediate need.

Literally, I am asking how to upload the new dataset (with a new flat file), and replace the old one.  And, if all my work (described above) will continue when the replacement dataset is uploaded.  A summary of steps to follow would be greatly appreciated.

Thanks!

[Vvelarde]

@irnm8dn

Hi, If you have a different file:

Sample:

Your file now is "WORKINGSHEET1.XLSX. And tomorrow you want to replace this with "WORKINGSHEET2.XLSX" without losing all the transform and manipulation in the data.

To do it this you have several ways:

1. Mantain an unique name of the XLSX File and everyday replace it with new data. (Always be WORKINGSHEET1.XLSX". Just Click on REFRESH and the data will be loading to Power BI DESKTOP.

2.  Change the source of the data. 

Step 1. Go to Data Source Settings

Step 2. Change the source of the file

Ok.  And is ready. If you want made a refresh action.

[blopez11]

If you used power bi desktop to load, shape and transform your file in the query editor, then you can just replace the older file with the new file, and perform a refresh in power bi desktop, then publish to the power bi service

[irnm8dn]

Thanks @blopez11 and I hate to make this more painful than it needs to be...

Am I simply launching Power BI desktop and clicking Get Data and uploading the replacement dataset?  Any step-by-step you would provide would be greatly appreciated.

Also, there is an "IF" statement that was created using the New COlumn function in the modeling tab, will this persist with the upload?

Thanks!

[Phil_Seamark]

Yep, that's all you need to do.

Either hit the publish button on the ribbon from Power BI Desktop, or from the Web Service find the Get Data option from the left hand menu and upload your local file.

You can use the personal gateway later when you want this scheduled automatically (all for free)

[irnm8dn]

Do I need to be conscious of file names, or am I simply using Get Data, Browsing and Uploading the replacement file.  Sorry I am being so literal.

[blopez11]

As @Vvelarde said, on your file system, you can replace the current working file with the new one, keeping the original name, then launch power bi desk top and perform refresh

Or in power bi desktop, in the query editor, you can modify the query source to point to the new file

In your case, short term, I would say option 1 is easiest

Hope this helps,