Miguel Escobar Published April 4, 2019

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

Power BIPower Query

IMPORTANT: You can read the official documentation that Microsoft has on this topic from the following link (url).

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:

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}})
#”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:


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


The code:

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}})
#”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?

Power BIPower Query
Notify of
26 Comentario
Newest Most Voted
Inline Feedbacks
View all comments
Frank Tonsen

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

I guess you meant: SharePoint.Tables

In this part of the MS World Tables are Lists and Folders are Tables 🙁

Prashant Panchal

Thanks, Miguel for amazing and informative blog post! I follow the dynamic file path method as per Ken’s article by creating Parameters table in Excel, how do I resolve issues when I open same file while my one drive is active on windows? it shows a weird file path inside Excel parameters table, something like this, instead of the hard drive file path?

https://d.docs.live.net/8ccdafa2f2b99a2b/ExActProBi/Consulting/APP/APP MIS Report/APP MIS Report/SourceData

I have also linked the image of the same parameters table below

Anil Kumar Khathraji

Is it possible to pull the all file in a folder using this method?

Daniel Huang

I am trying to do the same. So there are multiple folders in my OneDrive, but I only want all the files in one of the folders and combine.

Pavel K

Article name makes to think that this works for Excel PowerQuery, and it doesn’t

Prashant Kumar Gupta


The steps are working fine for me, but when i am sharing the report with users, they are getting refresh error “Recource Access Forbidden Exception”. Is there any workaround on this?

Chris Hartnell

Does this work for PowerQuery (as opposed to PowerBI?) – PQ has an option to select from a ‘sharepoint list’ rather than folder. The following code does let me into the SharePoint site I want and I then need to apply a series of filters to get down to the folder and files I want:
Source = SharePoint.Contents(“https://companyX.sharepoint.com/sites/RootSiteName/SubsiteNameX/”),
#”Filtered Rows” = Table.SelectRows(Source, each ([Name] = “FirstFolderName”)),
and repeat

What I want is to append the data from a Table from each file in the folder but I can’t see a way of doing that without using the more normal ‘Get files from folder route’ and using a temporary mapped drive approach.


Hi Miguel,

Thanks for the great post. I was tearing my hair out trying to achieve this and ended up using the Sharepoint.Contents function which was what I was expecting to have through a nice GUI given it’s MSFT > MSFT!

Best Regards,


How do I connect to a file if I only have permissions to that file? I dont have permissions to the entire site?


how i can pass dynamic file name in power query while connecting to the sharepoint


I miguel, your articles are very interesting.

I have a problem becouse when I enter the URL of my share point in order to connect the systems Power Bi remands me that ” the connection wasn’t possible” because the “access to the resourse it ins’t allowed.

Could you help me ?

thank you in advance


I miguel, your articles are very interest
I need to load excell files from various sharepoint sites.
Publish on powerbi service and used autorefresh.
The list of files to load is dynamic.
This list “FILETOLOAD” is a excel file with rows : FILENAME | SHAREPOINTPATH .

At one point I plan to use Web.Contents(“based url path” , [RelativePath=”filename.xls”] to be in position to use the autorefresh in Power service (o avoid message on dynamic data) but I got the message “DataFormat.Error: The input URL is invalid. Please provide a URL to the file path on SharePoint up to the file name only (with no query or fragment part)”

With all these constraint , it appears that the only solution is to make various get sharepoint folder (so load all the files in the sharepoints even the one I don’t need) and then filter these tables with the list FILETOLOAD.
After this process I will have a Table with Binary | Filename and I can add table invoking custom function to loop on all the files.
I would appreciate your feedback




Hello, thank you this was very informative. One query, I have to make a database using Excel online, which will be saved as a file on Sharepoint (shared with the whole team as well i guess) and i want to use the link of a file for a offline microsoft excel file in which i want to draw the data using query. And then i want share that file with the team.

So two queries in this, how do i get the excel online link to import data and in case it uses my my sharepoint which also has my ID in the url, will the offline file work/refresh for others

Marvin Axsom

First let me say that “M is for (DATE) Monkey is a very powerful toll for all levels of ExcelPros…
Now that that’s out of the way.

I am trying to use your tip from ch.23 “Dynamic Parameter Tables” with the addition on to the file path’ &”Source File” ‘(from the books example) changed by a Data Validation List that the user selects from a different tab. Essentially having them change the folder that all the data is collected/stored.
So they can pull reporting across different data file sets.
Allowing them the flexibility to retrieve the reports of their choosing.

All the files are stored in sub-folders from the same SharePoint basically the format is
https://mycompany.sharepoint.com/sites/rootfolder/ shared/reportfolder/data/ (this is where the subfolders are).
I have added my Parameter in to this line (the end of the 3rd line counting the Source line)
= #”Filtered Rows”{[Name=”data1.csv”,#”Folder Path”=(fullfilepath)]}[Content],
where (fullfilepath) is my parameter of course…

The problem is nothing changes – after a refresh it is still pulling from the same folder. I have tested the function and am getting the correct folder path when a different selecton is made from the validation list.

Help Help help…

With only a week of experience with Power Query, I hope that by utilizing your resources I am able to use it to it capabilities…


Hi Miguel, once you have all the binary contents, rather than combining them all, I ‘d like to crate separate tables for each binary row because in my SharePoint folder I have different tables with totally different contents, rows and columns. How to do that please?