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!