Automating refreshing of Power BI Reports and Dashboards with Excel Workbooks and Power Update

I recently tested to see if it was possible to upload an Excel Workbook to One Drive for Business, and then use this within Power BI. This was successful.

Then I saw, and it appeared that if you used an Excel Workbook from One Drive for Business, that it would automatically refresh the file. Which I thought it might possibly mean that if you updated your Excel Workbook in your One Drive for Business folder it would then update your dashboards and reports in Power BI.

The outcome was that it does indeed do this. Which means that we not have a means to update Power BI dashboards and reports from our Excel Workbooks which can be stored On Premise.

This also gives us the ability to take any source information that we can get into the Power Pivot Model and get this to update in Power BI. So this also means that anything that we do with Power Query, which then goes into our Power Pivot Model (Data Model) can then be updated in Power BI!

And below I will explain how to do this using Power Update

NOTE: The reason is you could manually update your Excel Workbook, and then save it to your One Drive for Business. But ideally if we can automate the process, this then means that we can have our Power BI Dashboards and reports updating automatically.

NOTE II: If you would like to know more about Power Update, please read the following Blog Post Power Pivot Pro (Rob Collie): Introducing Power Update

• Within the version they do have a free option!

Example Data:

• We are going to use an Excel workbook which I used previously which had some Adventure Works data. The details of where the Excel Workbook are is listed below.

Uploading Excel Workbook with Power Pivot and Power View to One Drive for Business

Below are the steps where I upload my Excel Workbook, which has a Power Pivot Data Model as well as a Power View Report to One Drive for Business.

1. The first thing is that I created my Excel Workbook, with Power Pivot and Power View.
2. NOTE: The reason that I created this is so that when it is uploaded to Power BI, the entire Power Pivot Model will be exposed in Power BI.
3. In order to do this, please refer to my previous blog post to create it with your own data
   1. Power Pivot and DAX in Power BI
4. Next I uploaded my Excel Workbook to One Drive for Business
   1. 

Importing your Excel Workbook from One Drive for Business into Power BI and create a simple report and Dashboard

Next we will import our Excel Workbook using the file which we uploaded into One Drive for Business within Power BI

1. Log into Power BI
2. Then click on Get Data
   1. 
3. Once this opens, make sure you select Excel Workbook, then click on Connect
   1. 
4. Now on the next screen you will see the options for Computer, OneDrive – Personal, OneDrive – Business
   1. Click on OneDrive – Business
   2. NOTE: The first time it might prompt you to log in and authenticate you to your OneDrive for Business Folder
   3. 
   4. Then go to the location where you saved your Excel Workbook in the previous steps.
   5. 
   6. Then click
      Connect
   7. NOTE: As you will see above currently my Modified Date is from 5 days ago.
5. Now under Datasets click on your Excel Workbook you just uploaded.
   1. 
   2. Click on Explore
   3. NOTE: You will also see that the Last refresh succeeded is Tue Apr 28 12:11:34 BMT +1000
6. We are going to create a very simple report and then a dashboard
   1. 
   2. We then saved this report as AW Auto Update
   3. 
7. We then and created a new Dashboard with the same name as above.
   1. NOTE: If you create a dashboard with the same name as your report, it will automatically put the pinned
      items into that dashboard.
   2. 
8. We then went back to our report from step 6 and pinned both items to our dashboard, as shown below:
   1. 
9. Now we are at the point to use Power Update and to test the auto updating of Excel Workbooks from One Drive for Business

Using Power Update to update our data as well as Upload to One Drive for Business

In this next section we are going to configure Power Update, to refresh our data in our Power Pivot Model. And then automatically upload it into our One Drive for Business Folder

NOTE: You can download and find out how to install Power Update from this blog post by Power Pivot Pro (Rob Collie): Introducing Power Update

• Within the version they do have a free option!

1. Due to the way I brought the data into my Power Pivot Model in my Excel Workbook, I did go and update my source query to the AdventureWorksDW2014 database.
   1. But you would normally have your query dynamic where it is getting its source data from.
2. For our example to show the automatic refreshing of data from One Drive for Business into Power BI, I am going to ensure that we have Sales for 2010 – 2013
3. In the next steps I am going to show you how to configure Power Update to refresh your Excel Workbook.
   1. Open Power Update
   2. Click on New
   3. Give your Task a Name
      1. In our example it will be called AW Auto Update.
      2. 
      3. Click Next
   4. For our example we set it to Daily (or Multiple times a day)
      1. 
      2. Click Next
   5. On the Setup a daily schedule we just accepted the defaults
      1. 
      2. Click Next
   6. On the Set up multiple times per day schedule, we set ours to run every 1 hour
      1. 
      2. Click Next
   7. Now on the step for select Destination Type, ensure to select SharePoint
      1. NOTE: Even though we are using One Drive for Business the underlying
         data is stored within SharePoint. So it will work.
      2. 
      3. Click Next
   8. For our example we only have a single workbook, so on the Select source workbooks, we selected Update a single workbook
      1. 
      2. Click Next
   9. On the Select workbook to update, select the location of your Excel Workbook on your file
      system or shared folder.
      1. 
      2. Click Next
   10. Now on the SharePoint Settings, we have to configure the following.
       1. First click on New under Credentials
          1. Make sure you put a tick next to Office 365 / Power BI.com
          2. 
          3. Click
             Ok
       2. Now on the SharePoint Site URL you will need to put in the following:
          1. Go to your One Drive for Business Folder in your browser
          2. NOTE: Copy and paste the entire URL, Power Update is smart and it knows which part of the URL it requires.
          3. Then click on Select
             1. Now browse to the folder location where you previously uploaded your Excel Workbook in the previous section: Importing your Excel Workbook from One Drive for Business into Power BI and create a simple report and Dashboard
          4. So that once completed it will look similar to the following below:
          5. 
       3. Click Next
   11. Now the Wizard is completed and you should see the following in Power Update
       1. 
4. Next we are going to manually run this now, so that we can show how it will auto refresh the data in Power BI once the process is completed.

Automatic Refresh of Power BI Report confirmation

Below we will see if Power BI refreshed the Excel Workbook from our One Drive for Business

1. The first place to see if our Excel Workbook has been updated via Power Update
   1. 
   2. As we can see above it has been updated.
2. Now we will have to wait and for Power BI to go and check the Excel Workbook and update the reports and dashboard
   1. NOTE: From what I read, the Dashboard tiles auto update every 10 minutes, I am not sure what the schedule is for Excel Workbooks in One Drive for Business
   2. But there must be some mechanism in place where it looks and updates Excel Workbooks connected to One Drive for Business
3. I then went back to my Datasets and clicked on the Ellipses button and after about 4 – 5 minutes I saw the following:
   1. 
   2. As you can see above the Last refresh succeeded time has changed from our previous value to Tue Apr 28 12:58:13 GMT +1000
4. Now I then went back to our Dashboard and saw the following below
   1. 
   2. As you can see from above, we now have the year 2010 (albeit small) as well as the increase in Sales
      Amount.

       

So in conclusion you now have a process to fully automate getting data from your On Premise source Excel
Workbook, and getting your reports to Automatically update in Power BI.