How to Connect PowerQuery in Power BI or Excel to a Sharepoint file.
Introduction to Connecting Sharepoint files to Power BI using PowerQuery
Being able to connect Power BI to files on Sharepoint is a really useful skill. While connecting to files on OneDrive or a hard drive is useful, for business continuity, Power BI or Excel reports based on datasets stored on these drives is hopeless. That’s because only one person easily has access to these drives. If they’re not around (ill, hollibobs, left the company etc) then what happens? The report falls over. Have a read of this article for more.
Any company that has Sharepoint has an easy solution though for business continuity. Simply store the supporting datasets on Sharepoint: then anyone can update the datasets and consequently the Power BI report.
So – how do you connect Power BI or Excel to a Sharepoint file?
Connecting to a Sharepoint File through the Excel connector, Folder connector or Sharepoint Folder Connector
There’s three ways that you can connect a Power BI report to a Sharepoint file:
a) the Excel file connector,
b) the Folder connector and
c) the Sharepoint Folder
You can also connect through the Web connector, but we’re not going to cover that one in this article.
The Excel File Connector or Folder Connector
You might have tried to use the Excel File or Folder connector to access Sharepoint files previously and not quite managed it. When you browse for your desired dataset in Power BI, PowerQuery opens Windows Explorer. It will help then to have Sharepoint synced to OneDrive (and hence Windows Explorer) before attempting to find the file or folder that you want to connect to.
To do this, go to the Sharepoint folder that holds the datasets you want to connect to, then on the ribbon, click Sync and follow the instructions. For additional instructions on this then click here to be taken to the Microsoft homepage.
You should then be able to find the file or folder that you want to connect to by PowerQuery.
The Excel File Connector or Folder Connector and Scheduled Refreshing
The Power BI service can handle scheduled refreshing, allowing you to set times during the day that you would like Power BI online to automatically refresh your report for any changes to the underlying datasets. However Power BI needs to always have access to your dataset to do this and the Excel file or Folder Connector does not permit this. This is generally because PowerQuery doesn’t expect you to use those connectors with a drive that is always on. If you connect to a Sharepoint file using either ot the Excel file or Folder connectors then you’ll need to use a Gateway. This is a piece of software downloaded to a computer that permits Power BI online to access datasets ‘on premise’. The Gateway only works when the machine it is installed on is on and logged in. Assuming that the Gateway is installed on an employee’s computer, then this reduces the period that the reports can be refreshed (probably not overnight or at weekends). It also reduces business continuity capability as the employee, whose computer the Gateway is installed on may not be around.
The advantage of connecting to Sharepoint datasets through the Sharepoint Folder connector is that you don’t need a gateway as Sharepoint is a cloud system and is always on. The Sharepoint Connector considers this and allows Power BI online to always access the datasets stored on Sharepoint, day or night, regardless of whether any computers are on.
The Video
- 00:20 - What are my connector options?
- 00:45 - How can I see my Sharepoint files when browsing for them in PowerQuery?
- 01:20 - What's the disadvantage to using the Excel file or Folder Connectors?
- 01:30 - What is a Gateway?
- 02:07 - What is the Sharepoint Folder Connector?
- 02:31- How do I use the Sharepoint Folder Connector in Power BI?
- 03:02 - What is a Sharepoint root URL?
- 06:08 - How can I find the exact folder on Sharepoint that I want?
How to use the Sharepoint Folder Connector in PowerQuery
To use the Sharepoint Folder Connector, go to:
a) Get Data and in Power BI, click More at the bottom of the menu. The Sharepoint Folder option pops up in the right hand side. In Excel, go to Data, then Get Data and then From File. From Sharepoint Folder is at the bottom of the menu list.
b) Put in the root URL of the Sharepoint folder that you want to connect to. This IS NOT the actual folder address you want to connect to but the parent folder of the folder you want to connect to. Sharepoint root URLs should generally look something like this:
‘https://companyname.sharepoint.com/sites/site-name/
The ‘companyname’ part should be the name of your company, while the site-name will often by a department site like ‘Finance’ or ‘IT’. IT teams or whoever administers Sharepoint will probably restrict access different site and therefore make sure you’ve got access to the Sharepoint site before you try to connect through Power BI. Don’t use ‘www.’ and don’t forget to put the ‘/’ at the end of the root URL.
c) Power BI may then ask you to sign in to Sharepoint. Select the Microsoft Account option on the left hand side of the pop up menu and click Sign In. If this doesn’t work then contact your IT team.
d) The Navigation menu should then pop up showing a list of all the files in Sharepoint underneath that root URL. Select Transform Data to be brought into the PowerQuery Editor.
e) From here the Sharepoint Folder connector acts like a normal Folder Connector and the PowerQuery Editor presents a list of files available in the selected folder (the root URL is the folder!) from which to select the folder that you want. To select the folder that you want, scroll to the far right of the PowerQuery Editor to see a column called Folder Path. You can search here for the folder pathway that you want by selecting the drop down menu and then either searching manually through the folder pathway list or using the Text Filters option to tell Power BI part of the text string of the final folder of the folder pathway along with the ‘Contains’ option. If you use the Text Filters option, don’t forget that PowerQuery is character sensitive, so a misspelling, incorrect capitalisation or trailing spaces will mean that it won’t find the right folder.
f) Once you’ve found the folder that you want, you can then go to the Content column (the first column in the dataset) and select the Combine option in the top right hand corner of the Header. From there you can select the sheet you want to connect to in each of your files and then PowerQuery will do the rest!
You’ll end up with 5 queries – 4 Helper queries, which you can ignore for the moment, and then your original query which extracts all the data from all the files that you clicked to Combine and appends them all together, ready to receive your transformation instructions.
Wonderful.
The Disadvantage of using the Sharepoint Connector
Be warned.
Using the Sharepoint Connector in PowerQuery is slow, much slower than using an Excel File or Folder connector. It is slow both to author queries in PowerQuery and slow to refresh data.
If you don’t need the scheduled refresh capabilities, it may well be a better solution to use the Excel File or Folder Connector options as it will take you MUCH less time.
Another solution is to perform all the queries using a Folder Connector and then convert the queries so that the Source step is using the Sharepoint Connector. Depending on the size and amount of queries, this could save a lot of time in the long term.
.... and a little more!
Connecting to a single file – There’s no connector to connect specifically to a single file in a Sharepoint folder. However you can get round this. Once you’ve found the folder path you want, then go to the name column and filter this column to the rows you want. You can then click on ‘Combine’ in the Content column in the normal fashion even if there is only one file you want.
The downside to this technique is you end up creating the Helper queries that Power BI automatically creates when it connects to a folder. This means that you have 5 queries instead of 1. There are further ways round this too and we’ll be posting about this shortly.