Creating an online Power BI Dashboard, using an Excel file hosted on SharePoint as source

This article was recently also published on devjef.wordpress.com:

 

Power BI gives you a great advantage, for example when you use it to get quick insights into your data. That’s one of the reasons our business analyst fell in love with this tool pretty quickly after she started using it. It almost completely replaced Tableau and Excel-based reporting in her daily routine.

Another great feature of Power BI is that there are so many data sources and connectors (adapters) to get data from all kinds of sources. But some of these connectors almost made me pull my hair out yesterday, so I’d like to share this learning with you, and hopefully it will save you some time and frustration 🙂

Excel as source
The dashboard I wanted to created would show the technical debt we have within our platform. For now the is just a test dataset, but in the future this will be used to measure technical debt over time, to get more insights into that.

So the first step is to create an Excel file, and prepare that for use in Power BI. You start out by creating an Excel file with some data in it:



But to allow Power BI to read this file, you need to use the “Format as table” feature in Excel. You do this by selecting your dataset, and click on the button “Format as table”, and select a style:



When you’ve done that, your sheet will look like this (depending on the style you picked of course):



The file is now ready to be stored on SharePoint. In my case, I’ve created a directory called “Technical Debt Dashboard” in the root of our shared documents:



Adding datasource to Power BI Portal
If you go to the Power BI Portal (app.powerbi.com) and log in, you can click on the button “Get Data”, and click on the button to attach your Excel file:



The next screen shows you the sources that you can use to host your file on. Of course, we choose SharePoint:



In the next popup window, you can enter the URL of your SharePoint:



If you paste in the base-URL (like I did), you can drill down in the next window you’ll see. Also, it will prompt for credentials to login to SharePoint if needed.

When you selected the file you want to use, click on connect:



The next question you get is how you want to import the data, or just connect the datasource. In this case, we want to click on “Import”:



Now the Power BI portal will process the contents of the Excel file, and a dataset will be created:



Okay, you can relax now, because the annoying work is over. We will now move into getting the data in your Power BI, so: the fun stuff!



Using the dataset in Power BI
In Power BI Desktop you can add the data by clicking on “Get Data”, and pick the option “Power BI Service”:



In this window you will see all of the available datasets (in my case of my own workspace). Click on the datasource you want to use (in this case “TechnicalDebt”):



The data is now imported and ready for use in Power BI Desktop:



For this example, I’ve added a simple gauge and added the value “Backend Technical Debt” as a value:



Now that my dashboard is done, I can upload it to the online portal by pressing the “Publish” button. But this probably doesn’t come as a surprise to you.



Refreshing the data
One if the pitfalls I encountered during my quest to get this to work, was the refresh time. On several occasions the data wasn’t refreshed, even if I tried to do that manually in the portal. But after some digging a colleague of me DID actually RTFM.

The Microsoft docs state the following:

“Because both Power BI and OneDrive are in the cloud, Power BI connects to your workbook file on OneDrive about every hour. If any changes are found, your dataset, reports, and dashboards are automatically updated in Power BI.”

In my first test, it took more than 30 minutes, so I assumed it didn’t work. In later tests it took less than 10 minutes for Power BI to refresh the data. So don’t panic when you don’t see your changes like I had. More info on that can be found in the Microsoft Docs article Get data from Excel workbook files.



Conclusion
TLDR; Maybe I should read the (f******) manual more often 🙂

Some of the Power BI Desktop functions could be more clear, for example the URL box. You can provide a URL to your SharePoint, but I couldn’t point it to a directory directly. Maybe it’s because the “Shared Documents” is not a directory but a site within a site. But after a few minutes I used the SharePoint base-URL, and that was a perfectly fine work-around.

Another issue I would like to raise (especially to you Microsoft, in case you ever read this): PLEASE GIVE US A POWER BI DESKTOP FOR MAC!!!

One of the reasons I used the shared Excel file as a source, was to work around the whole issue that there’s no version of Power BI Desktop available for my colleagues that work on a Macbook. And I think there are a lot of other users out there who would really like to get their hands on Power BI Desktop for Mac, not just our company…

And thanks again Koen Verbeeck (Blog | @Ko_Ver), for the mental support and for your ideas that pointed me in the right direction! 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *