Connecting to Files in SharePoint & OneDrive with Power BI / Power Query

I’ve been trying to join multiple Facebook communities that revolve around Power BI topics.

I was able to join a couple communities that are completely neutral in the sense that they’re not run by a for-profit company, but rather just community members which make things easier as there’s little chance of a conflict of interest with the admins of the group.

One of these groups is called “Power BI Latinoamerica” which is a Community that primarily speaks the Spanish language and within that group one of the admins posted a video that caught my attention:

It’s basically a video that showcases a way to connect to an Excel file that is being hosted on OneDrive and while that method is completely valid, I was trying to reference the author of that video to one of my articles about connecting to files hosted on SharePoint and OneDrive and then I realized that I haven’t formally wrote about that topic in my blog…ever.

Disclaimer, I’ve created multiple videos about this for some of my online courses, so you might’ve seen this method before if you’ve followed any of the courses where I participate.

It’s time to change that! Let’s find out what’s the easiest and most optimal way to connect to ANY file hosted on OneDrive or SharePoint.

What is SharePoint? What is OneDrive?

In this blog post we’re going to focus on just 2 data sources:

• SharePoint –  a web-based collaborative platform that integrates with Microsoft Office. You can create group sites or subsites where you can share documents with your colleagues. Learn more about SharePoint here.
• OneDrive for Business – you can imagine this as your personal folder within a Company account. It provides the same end-user experience that you get from SharePoint, but with restrictions so it’s only accessible to you and in this sense is private or personal to you. It is part of the Office 365 experience, and you can learn more about OneDrive here.

Both can use the same Power BI / Power Query connector and authentication method which makes things easier since the connector does provide a really cool experience for the end user as well as the security needed.

How to Connect to SharePoint with Power BI / Power Query?

The Goal:  Connect to a specific Excel file stored in a SharePoint Teams / Groups site

In our case, we have the following site:

As you can tell, this is a Office 365 Group Site that could be transformed or converted to a Teams site without any issues.

The following method of connecting to this folder applies to subsites, groups and team sites.

The only thing that we need to make everything work is simply go to the url of the site and simply select the portion that has the format like this:

https://<<sharepoint site root url>>/sites/<<group name, sub site name or teams site name>>/

which in my case is this one:

Now I can head over to Power BI and select the connector that reads “SharePoint Folder”:

and this will require you to input a url and, as described above, it has to have the format that I showcased above:

Note: if you wish to connect to the root directory of your SharePoint site, you only need to enter the root url of your Sharepoint site for this connector.

and once you hit OK you’ll be prompted with the Authentication window in which you’ll need to select the Microsoft Account, then click the Sign in button and sign in:

After you hit Connect, the result will be a similar window to the one that you get when you use the “From Folder” connector:

and in reality, it is almost the same experience where the only difference is that your files are hosted on SharePoint and not on a local folder.

You can click Edit to see the data in the Power Query Editor and you’ll notice that even the names of the columns are exactly the same ones that you see when you connect to a local Folder with Power BI / Power Query:

and if you wish to connect to a single file then all you have to do is simply click on the Binary of your choice and you’ll be good to go. In my case I went ahead and selected the “03-March.xlsx” file and this was the output after clicking that binary and selecting the data that I wanted from that file:

Here’s how that code looks like:

let
Source = SharePoint.Files(“https://powerbipanama.sharepoint.com/sites/externalsales/”, [ApiVersion = 15]),
#”03-March xlsx_https://powerbipanama sharepoint com/sites/externalsales/Shared Documents/Sales Report/” = Source{[Name=”03-March.xlsx”,#”Folder Path”=”https://powerbipanama.sharepoint.com/sites/externalsales/Shared Documents/Sales Report/”]}[Content],
#”Imported Excel” = Excel.Workbook(#”03-March xlsx_https://powerbipanama sharepoint com/sites/externalsales/Shared Documents/Sales Report/”),
#”03-March_Sheet” = #”Imported Excel”{[Item=”03-March”,Kind=”Sheet”]}[Data],
#”Promoted Headers” = Table.PromoteHeaders(#”03-March_Sheet”, [PromoteAllScalars=true]),
#”Changed Type” = Table.TransformColumnTypes(#”Promoted Headers”,{{“Month”, type text}, {“Name”, type text}, {“Australia”, type number}, {“Canada”, type number}, {“Central”, type number}, {“France”, type number}, {“Germany”, type number}, {“Northeast”, type number}, {“Northwest”, type number}, {“Southeast”, type number}, {“Southwest”, type number}, {“United Kingdom”, type number}})
in
#”Changed Type”

The key element here is the SharePoint.Files function, but you can even use another function!

You can use a function by the name of SharePoint.Contents which will give you a completely new experience which looks more like a way to navigate through your folders in the event that you have way too many files.

This would be the experience with the SharePoint.Contents function:

1. You connect to your site
2. You navigate to Shared Documents Folder
3. You navigate to the specific folder where your data is being stored
4. You see all of your files inside the folder that you wanted

How to Connect to OneDrive with Power BI / Power Query?

The Goal:  Connect to a specific file stored in a OneDrive

In our case, we have the following OneDrive on Office 365:

Similar to what we did before, we have to look at the url in our browser:

the format in this case is almost the same, where we do have the root url, then “/personal/” and last but not least the name of the user which would be different in every case.

In my case, that would be this:

https://powerbipanama-my.sharepoint.com/personal/miguel_escobar_poweredsolutionsonline_com

And I can use that value inside the SharePoint.Contents or the SharePoint.Files functions to grab the data that I need:

The code:

let
Source = SharePoint.Files(“https://powerbipanama-my.sharepoint.com/personal/miguel_escobar_poweredsolutionsonline_com”, [ApiVersion = 15]),
#”01-January csv_https://powerbipanama-my sharepoint com/personal/miguel_escobar_poweredsolutionsonline_com/Documents/Sales Data/” = Source{[Name=”01-January.csv”,#”Folder Path”=”https://powerbipanama-my.sharepoint.com/personal/miguel_escobar_poweredsolutionsonline_com/Documents/Sales Data/”]}[Content],
#”Imported CSV” = Csv.Document(#”01-January csv_https://powerbipanama-my sharepoint com/personal/miguel_escobar_poweredsolutionsonline_com/Documents/Sales Data/”,[Delimiter=”,”, Columns=12, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#”Promoted Headers” = Table.PromoteHeaders(#”Imported CSV”, [PromoteAllScalars=true]),
#”Changed Type” = Table.TransformColumnTypes(#”Promoted Headers”,{{“Month”, type text}, {“Name”, type text}, {“Australia”, type number}, {“Canada”, type number}, {“Central”, type number}, {“France”, type number}, {“Germany”, type number}, {“Northeast”, type number}, {“Northwest”, type number}, {“Southeast”, type number}, {“Southwest”, type number}, {“United Kingdom”, type number}})
in
#”Changed Type”

What are the benefits of these methods?

There are 4 major benefits to using this method over any others:

1. The connector is already a native part of Power BI / Power Query, so any issues that you might encounter fall under the Microsoft Support.
2. You can connect to multiple files instead of just one when connecting to your files this way.
3. You reduce the risk of ever hitting a Formula.Firewall since all of the files inside that folder will be set with the same Privacy Level and will fall under the same scope
4. The SharePoint connector was created in a way where it uses the SharePoint API to grab the data. The connector also allows, to a certain degree, Query Folding so when you run your query you’re only getting the files that you’re actually interested in and not all of the files from your SharePoint site. You can read more about Query Folding here.

There are other benefits that might be specific to certain cases, but these should give you an overview of the main reasons why you should try using this method instead of the any other ones.

Have you ever used the SharePoint.Contents function? did you know that this same experience is also available for local folders with Folder.Contents?