How to Connect to an Excel File Stored in SharePoint Online from Power BI Desktop

Introduction

You might have come across a scenario where you need to generate Power BI Report from data stored in an Excel file that is stored in SharePoint Online library. How do you do this? If it's an SQL table, SP list, Local Excel or CSV file, MySQL DB or any other database, you could do it easily from Power BI Desktop by using already provided connectors. But there is no direct connector available to connect to an excel file stored in the SharePoint online library.

Solution

The solution is to use a Web connector. Open Power BI Desktop >> Click on Get Data >> Select Web option >> Enter the URL of the Excel file stored in your SharePoint library >> Keep selection as Basic >> Make sure you enter the URL in below mentioned format >> Click on Ok

 

Make sure you use an Organizational Account option to authenticate. Other authentication mechanisms won't work while doing this. As shown below, click on the Sign In button, and once signed in with your office 365 email ID and password, click on Connect

 

Once signed in and when you click on Connect, you will see tabs/sheets/tables from your Excel file as shown below. Select the tables as per your need and click on load. If you need to transform your data, then click on Edit and do the necessary transformation.

 

That’s it. Hope this helps. Thanks for reading!



Next Recommended Reading How To Clear Cache Of Power BI Desktop