Quick Tips: Connecting to Excel Files Stored in SharePoint Online from Power BI Desktop

Connecting to Excel Files Stored in SharePoint Online from Power BI Desktop

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:

  1. Select the Excel file
  2. Click the Open button
  3. Click Open in app
Opening Excel file from SharePoint Online in Excel Desktop App
Opening Excel file from SharePoint Online in Excel Desktop App

This opens the Excel file in the Excel Desktop application. In the Excel follow these steps:

  1. Click the File menu
  2. Click Info
  3. Click the Copy path button
Copying Excel Path from Excel Desktop App
Copying Excel Path from Excel Desktop App

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:

  1. Click Get data
  2. Click Web
Getting data from Excel stored in SharePoint Online in Power BI Desktop
Getting data from Excel stored in SharePoint Online in Power BI Desktop
  1. Paste the path we copied from Excel in the URL text box
  2. Delete the ?web=1 from the end of the copied path
  3. Click OK
Modifying the path copied from Excel to get the data in Power BI Desktop
Modifying the path copied from Excel to get the data in Power BI Desktop
Continue reading “Quick Tips: Connecting to Excel Files Stored in SharePoint Online from Power BI Desktop”

Data Visualisation with Power BI Desktop

As most of you guys know Power BI Desktop is released. I should say, it’s awesome. There are heaps of changes in compare with its preview edition Power BI Designer. I’ve written a series of posts regarding creating a report and dashboard using Power BI Designer before. You can find them here. Now I want to explain the same thing in Power BI Desktop. I’ll cover lots of new features in this post and I hope you enjoy it.

Get Data

  • Open Power BI Desktop
  • Click on Get Data. You can also get data from recent data sources or even open a predefined report stored in pbix format

Power BI Desktop 01

  • We use Adventure Works DW 2012 database as sample, you can open your real world data source
  • Click on “SQL Server Database” then “Connect”
  • In this sample we are connecting to a “SQL Server Database”
  • Click “Connect”

Power BI Desktop 02

Continue reading “Data Visualisation with Power BI Desktop”