Recently, when teaching the Advanced Power BI Course by Pragmatic Works, I had a student ask how to use the SharePoint Folder in Power BI. She had been struggling with getting it to work correctly. After trying a few different URLS I could see why she was having a problem. Since you’re reading this, you are probably having the same kind of issues. The error message is actually pretty good, but a little confusing. When the error says “the site’s root URL only.” I took this to mean SharePoint’s URL, however, it actually means the URL of the site within SharePoint.
The URL isn’t valid. Please enter the site’s root URL only.
Let’s take a look at how you can properly connect to a SharePoint Folder in Power BI and extract the files.
Connecting to a SharePoint Folder in Power BI
In my first attempt I tried connecting to simply the root of my SharePoint site.
This only allowed access to the Shared Documents folder on the root and therefore didn’t work.
As you can see in the above image, this only allowed access to the Shared Documents folder on the root of the SharePoint site.
Connecting to a specific SharePoint site
Next, I tried connecting directly to the root of a particular site. I connected to the training site on SharePoint using the following URL:
This worked, the SharePoint folder option will traverse subfolders, just like the default from folder option. Therefore, if you are looking for a particular directory then you can simply apply a filter in the Power Query Editor on the Folder Path property.
Thanks for reading this blog!