How to connect to a SharePoint folder

[ShNBl84]

I want to connect to a folder on my company SharePoint and use it in PBI.

Using Get Data and then SharePoint folder, I can connect through my C drive and it all works, but it needs to be SharePoint based, not just on my computer.

The SharePoint folder link is: https://XXXXXXX.sharepoint.com/:f:/r/dataanalytics/Shared%20Documents/Administration/Pricing%20Compl...

Using the above link that SharePoint provides, I get an error:

I can get connected with the root site (https://XXXXXXXXXX.sharepoint.com/) and one folder deep (https://XXXXXXXXXX.sharepoint.com/dataanalytics/).

But I get an error when I try any of the below:

'https://XXXXXXXXXX.sharepoint.com/dataanalytics/Procurement/'

'https://XXXXXXXXXX.sharepoint.com/dataanalytics/Shared Documents/'

'https://XXXXXXXXXX.sharepoint.com/dataanalytics/Shared%20Documents/'

Has anyone been able to connect to a SharePoint folder?

[ShNBl84 · Accepted solution]

Not a great solution, but I came up with one.

First, I connected to the folder I needed by using Get Data and selecting Folder and browsed to the SharePoint folder on my C drive. I then selected "Combine & Transform Data" to prompt PBI to make all the helper queries that combine all the csv files I have in that folder.

Then, I made another connection and went as deep as PBI would allow me connecting to the SharePoint folder via Get Data > SharePoint Folder (https://XXXXXXXXX.sharepoint.com/dataanalytics/). I then filtered the "Folder Path" column to select only the final folder destination that I need. 

I opened the Advanced Editor in the C drive data and copied all the code except the Source line. I pasted that code below the second code line (first was Source, second was filtering to the folder I need) in the second connection (the SharePoint folder one) and made the appropriate name/source adjustments to get the code to fit.

So I now have a solution that connects to my SharePoint that gets only the folder I need, but I imagine a lot of unneeded processing is being done while PBI pulls my company's entire dataanalytics folder, then filters to just the folder I need thereafter. I am really surprised that there does not seem to be a more efficient and user-friendly way of doing it. I would love to hear if anyone has a better solution.

[Anonymous]

Here is great blog from Mitchell Pearson.

https://mitchellpearson.com/2018/06/04/quick-tips-connecting-to-a-sharepoint-folder-in-power-bi/

Regards,

Ferdinand

[Anonymous]

• See how "deeply" it lets you connect into the SharePoint.
• Use Text Filters - Contains to filter down to the file name you need.
• Click on only that one.

[Tad17]

Hey @ShNBl84 

Check out this thread where your question was answered: https://community.powerbi.com/t5/Desktop/Sharepoint-Folder/td-p/237546

Copied Answer:

"Based on my test, we have to list all available folders, then select those we need from the list. As mentioned in above link:

Now that we’re in, we’ll be able to see all of the files from all of the folders that we have access to in that specific site. This might be a pretty long list, so let’s do some filtering on the “Folder Path” column and select the folder where your data is being stored so you only get the files from it."

You may also want to check out this article: https://powerbi.microsoft.com/en-us/blog/combining-excel-files-hosted-on-a-sharepoint-folder/

If this helps please kudo.

If it answers your question please accept it as a solution.