cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
admiralman Member
Member

Sharepoint Folder

I would like to target a folder within a Sharepoint Online library similar to the "connect to folder" functionality that exists on the local client. Can anyone tell me if this is possible? I have seen some articles that state it is possible and they either are inaccurate or the method is a not feasible considering the performance would be horrible. Any help is appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
v-yulgu-msft Super Contributor
Super Contributor

Re: Sharepoint Folder

Hi @admiralman,

 

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.

 

If we direct to a specific folder in URL (https://microsoft.sharepoint.com/xxxx/xxxx/Shared%20Documents/Power%20BI), it will fail to connect and prompt below error.

3.PNG

 

 

Regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
13 REPLIES 13
Highlighted
Super User
Super User

Re: Sharepoint Folder

Hi @admiralman

 

If you open up your SharePoint site in Internet Explorer and then navigate to your Document Libriary and then click on the Ribbon, there will be an option to Open in Windows Explorer.

 

Once that is done, in the Power BI Query Editor you can then click on Get Data from Excel file and then use the URL and the file name and connect to the file.

 

Another option is you could possibly do it by clicking on Get Data and then selecting Folder. And then paste in the URL from your Windows Explorer.


Did I answer your question? Mark my post as a solution!
"Proud to be a Datanaut!"
Power BI Blog
admiralman Member
Member

Re: Sharepoint Folder

Thanks but I am not looking at targeting a specific file but rather the folder. I want to be able to basically say "for every file in this folder bring in the file, perform the necessary transformations, and merge the data into my dataset so I can report on it". There is this option when targeting files on the local hard drive.

Super User
Super User

Re: Sharepoint Folder

Hi @admiralman

 

You could possibly create a function for each file and then loop through each file into one dataset?


Did I answer your question? Mark my post as a solution!
"Proud to be a Datanaut!"
Power BI Blog
admiralman Member
Member

Re: Sharepoint Folder

Sure...there are a lot of ways to attack this programmatically. I could schedule a power shell to connect and consolidate all files onto one. Does no one know how the Sharepoint folder is supposed to work?
v-yulgu-msft Super Contributor
Super Contributor

Re: Sharepoint Folder

Hi @admiralman,

 

Power BI desktop provides a "SharePoint Folder" connector that allows us to get all files inside a folder. You could refer to below blog for detailed description:

Combining Excel Files hosted on a SharePoint folder with Power BI

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
admiralman Member
Member

Re: Sharepoint Folder

So if I have 10 thousand files in that entrlire site the report will have to cycle all of those in order to filter them and the process will be extremely slow...correct?
v-yulgu-msft Super Contributor
Super Contributor

Re: Sharepoint Folder

Hi @admiralman,

 

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.

 

If we direct to a specific folder in URL (https://microsoft.sharepoint.com/xxxx/xxxx/Shared%20Documents/Power%20BI), it will fail to connect and prompt below error.

3.PNG

 

 

Regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
irisyeong Regular Visitor
Regular Visitor

Re: Sharepoint Folder

Hi there,

 

I managed to retrieves for certain folders.  Using the same query, I just changed the url.  But, the same query (with modified url) is either not returning any return from other folders or returning incomplete list.  Why is that so?  

admiralman Member
Member

Re: Sharepoint Folder

Ok...need to revive this thread. I have managed to follow the steps in this post and using the desktop application can connect just fine and transform my data. However, when I deploy it to the powerbi service and attempt to refresh the dataset I get the following error. I have seen this error before on my desktop after an upgrade but a) am not seeing it now and b) only see it in the service. Any ideas?

 

 

Excel Workbook: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. The 64-bit version of the Access Database Engine 2010 Access Database Engine OLEDB provider may be required to read this type of file. To download the client software, visit the following site: https://go.microsoft.com/fwlink/?LinkID=285987. Table: Query2.

 

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 278 members 2,942 guests
Please welcome our newest community members: