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:
- Ensure that the file is reachable from SharePoint or OneDrive by navigating to it’s location through a browser
- Get the link to the file
- Modify the link so that it contains the appropriate Site URL
- Use the modified link to connect a new data source to Power BI
- Navigate to your file
- 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>.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>.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
The JPanda 