Ever since Excel made its debut in the 1980’s, it has been used as a quick way for end users to input and manipulate data on their own without going through the extensive data engineering and data ingestion processes. With Power BI coming on to the scene in 2015, it quickly became the go-to visualization tool for various data sources. These two powerful tools can be used together to drive customized insights for your organization. By uploading your Excel file into SharePoint/OneDrive, you can easily connect and set up a refresh to a Power BI report in the Power BI Service without an on-premises gateway.
The benefit of this method is that you can continue to update the Excel workbook from SharePoint/OneDrive and Power BI will pick up those data updates whenever the semantic model is refreshed. Another benefit to this method is that your laptop doesn’t need to be on for the refresh to work. If you have the Excel file on your machine and decide to use a gateway, the only way your refresh will work is if the computer that has the gateway installed is on. Also, by moving your Excel file to the Cloud, you get the benefit of easy sharing, automatic backups, and the ability to edit your file from any computer.
Alright, so now that I’ve sold you on storing your files more securely in the cloud, let’s discuss connecting to that data in Power BI desktop. Because the file is no longer on your machine, we can’t use the Excel workbook connector. Instead, we are going to use the web connector. But first, let’s start by opening our Excel file from OneDrive. There are two options for this, the first is to sync your OneDrive to your computer (highly recommend this option) and the second is to open the Excel file in your browser then navigate to open it in your desktop. If you’d like to sync your OneDrive to your laptop, go to the Settings gear in the upper right-hand corner and select “Sync this OneDrive”.
Now we can open the file in the Excel desktop app! Once you open the Excel file on your desktop, navigate to the File tab and go down to the Info section. Select the Copy Path button.
Alrighty now let’s get back to Power BI. In Power BI, select Get Data drop down in the top ribbon and click on Web.
Paste in the link we copied from Excel and delete the “?web=1” at the end of the URL.
And boom that’s it! Now you can choose what datasheets and/or tables from that Excel workbook you’d like to include in your report!
Okay I lied, one more step but it’s easy I promise. Once you publish your report to the online portal, you’ll need to set up the refresh. To set up the refresh, navigate to the workspace and select the three dots next to the semantic model you published. From there, navigate to the Settings page.
Now for the actual last step, select the Data source credentials drop down and select Edit Credentials. Finally, click on Sign In and sign in using your OneDrive credentials. And boom that’s it! Nice work!
Happy coding friends! 