Microsoft Excel is one of the most common data sources for Power BI. We can store Excel files in various storage types. The way we get data from Excel varies depending on the storage type. In this post, I quickly show two methods to connect to an Excel file stored in SharePoint Online.
Method 1: Getting the Excel File Path from the Excel Desktop App
This method requires you to have the Excel application installed on your machine. In this method, we open the Excel files stored in SharePoint Online in the Excel Desktop App in our machine and get the file path from there.
In SharePoint Online go to the desired document library then follow these steps to make it work:
- Select the Excel file
- Click the Open button
- Click Open in app
This opens the Excel file in the Excel Desktop application. In the Excel follow these steps:
- Click the File menu
- Click Info
- Click the Copy path button
So far we got the Excel file path. The step is to get data from the copied path in Power BI Desktop.
Open Power BI Desktop and follow these steps:
- Click Get data
- Click Web
- Paste the path we copied from Excel in the URL text box
- Delete the
?web=1from the end of the copied path
- Click OK