[Power BI] Connecting Power BI to an Excel File Stored on Microsoft Teams, SharePoint or OneDrive as a Data Source

Level of Difficulty: Beginner – Senior.


Power BI reports are a quick and easy way to visualise existing data. Often, we want to visualise data from flat files that aren’t necessarily stored on our local computer but rather, files that are modified by other contributors. By storing a file on OneDrive or SharePoint (or indirectly, on Microsoft Teams), the file may be shared with other collaborators and allows for easy data refresh on a Power BI report.

What are the steps?

The steps of connecting a Power BI report to an Excel workbook stored on SharePoint or OneDrive are as follows:

  1. Ensure that the file is reachable from SharePoint or OneDrive by navigating to it’s location through a browser
  2. Get the link to the file
  3. Modify the link so that it contains the appropriate Site URL
  4. Use the modified link to connect a new data source to Power BI
  5. Navigate to your file
  6. Transform and load the data

Navigate to Excel file to retrieve and modify the link

There are two ways to access the file and either way is dependent on where you’ve stored the file: OneDrive or SharePoint. If your document is stored on Microsoft Teams, it is accessible via SharePoint which means that if you follow the SharePoint navigation steps, you’ll get the same results.

OneDrive

In many instances, files stored on OneDrive are stored on the background instance of SharePoint so when you get the file link, it’ll have SharePoint in the URL. For this reason, even if the file is stored on OneDrive, we still use the SharePoint folder connector on Power BI.

Once you’ve retrieved the link to the file, make sure you grab the first part of the link that makes up the Site URL.

Original link:
https://<domain>/:x:/r/personal/jacqui_muller/Documents/Datasets/world_cities_with_timezone.csv

The link we need:
https://<domain>/personal/jacqui_muller/

Please note the highlighted :x:/r/ in the original link which needs to be removed when modifying the link into its required site URL form.

SharePoint

The document that I’m trying to access is currently stored in Microsoft Teams. I opted to open the document in SharePoint which has rendered this view for me to get the link from.

Original link: https://<domain&gt;.sharepoint.com/teams/Reactit/Shared%20Documents/General/world_cities_with_timezone.csv?d=w303802fbc1f04af2a4b27fdd251757a9&csf=1&web=1&e=cUU5Iz

The link we need:
https://<domain&gt;.sharepoint.com/teams/Reactit/

Connect to Power BI

Open a new instance of Power BI Desktop and select “Get Data“. From there, select the “SharePoint Folder” connector option and then click on “Connect“:

Now paste the modified URL obtained during the step followed in the previous section into the “Site URL” textbox and hit OK:

You may be prompted to sign in. Use the Microsoft Account option to log in using your account that has access to the file.

Once you’ve been authorised, you should see all of your files in the site appear (as seen below). In order to select the appropriate file as a data source, click on “Transform Data“:

A Power Query window should pop up, allowing you to select the Binary Content of the file you’d like to use. Click on Binary in the Content column, corresponding to the file you’d like to use:

Once you are able to see all of your data in the Power Query Window, along with the new navigation steps that were added in the Query Settings window, you may click on Close & Apply to start working with your data:

Did this work for you or did you run into any issues trying to connect to your file? Drop a comment below or reach out for assistance – jacqui.jm77@gmail.com

Published by Jacqui Muller

I am an automation architect by current profession who enjoys dabbling in software development, RPA, IOT, advanced analytics, data engineering and business intelligence. I am aspiring to complete a Master's degree in Computer Science within the next two years. My competencies include a high level of computer literacy as well as programming in various languages. I am passionate about my field of study and occupation as I believe it has the ability and potential to impact lives - both drastically and positively. I come packaged with an ambition to succeed and make the world a better place.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: