Forum Discussion
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.
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.
Regards,
Yuliana Gu
29 Replies
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.
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.
Hi admiralman
You could possibly create a function for each file and then loop through each file into one dataset?
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- 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?
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.
Regards,
Yuliana Gu
Thank you so much!!!! the solution below from you is the only solution that worked for me!!!!
"
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"I made it work by only granting access to the specific folder(s) on the Sharepoint site for the user that is creating the Power BI report.
So no access to the Home site of the Sharepoint site - only read access to the folder(s). Test by entering the URL used in Power BI in a browser and you should get Access denied.
Create new datasource in Power BI desktop using Sharepoint folder - enter Sharepoint site URL.
You will see a list of all files in the Sharepoint site but do not worry - choose Combine and Edit and you should only see the files in the folders you have read access to.
Thank you after hours of searching, you saved me so much time. In my case it was also helpful to export this Power BI query data to excel I used the following steps:
Step 1) Query all items and subfolders in a SharePoint Folder data using Power BI: Combining Excel Files hosted on a SharePoint folder with Power BI
Step 2) Tansform and Bulk Export (5000+) items to Excel: using Power BI Desktop: #PowerQuery – Easily Copy all queries from a PBIX to Excel and vice versa – Erik Svensen – Blog about Power BI, Power Apps, Power Query (wordpress.com)
This seems to be the ongoing query here, which is the issue I am experiencing.
Yes, I can connect to the root directory of my Sharepoint Site. Yet, Power BI Desktop seems to disregard some files (e.g. newer .xlsx files) and includes a huge number of other document types such as .pdf and .docx files - I cannot remove my write access to files as the report creator, which seems to be the only viable solution in this thread so far.
The original question from this post pertains to targeting a specific folder to connect to, so that all other files are not available. I am interested in targeting a particular .xlsx file that does not appear in my list of available files from my Sharepoint Site.
Is this even possible or not?
I have successfully added the workbook within the Power BI Service (hoping this would facilitate the connection process) and also verified that it is present in the Sharepoint directory (note: I do not want to target the file sitting on my local hard drive). I have successfully connected to 4 other .xlsx files and it's just this particular one I need that is just not showing up. I'm all out of ideas for workarounds to force the use of a Sharepoint file as a data source. The Advanced editor code doesn't seem to use specific URL address either (i'm using the cobine & transform option).
I would be open to modifying the connection if there is a way via the advanced editor, but I'm unsure where the reference to the specific file resides in the code. I have created additional dummy .xlsx files to see if they appear but they do not. Something to do with newer files? Doesn't make much sense to me.
Thanks in advance for your assistance.
Ryan
Hey Ryan Anonymous ,
If its not showing on the sharepoint folder connection have you tried refreshing the preview to see if it appears then?
i must admit it's not a particular issue that has arised for myself as of yet!
alternatively you can use the web data connector? find the URL for the file and use the web connector.
Please ignore the actual text in the image as it was only dummy data. If you use the fulla dress for the file in question once configured you will see in data source settings it will change itself to be a Sharepoint connection rather than a web connection or at least thats what has happened with myself in the past.
Thanks
Al
you may well be aware of the soultion now,
if you use Get Data and use sharepoint folder use the sharepoint site name and when in transform data navigate to the file you want
And what to do when you have two folders, each containing one file = two files total, but Get Data only sees one of the files?
We do not have folder level permissions set, version control is off. PBI should see two different paths and one file for each path. It doesn't.
Working with SharePoint seems a lot more painful than it should be.
Are you using a file specific URL for the data connector or using the sharepoint site URL
i tend to use the latter and navigate to the file, the only other thing i can think of is if they are 2 different files? i.e. xlsx & xls
normally sharepoint folder data connector seems to remove the idea of foldesrs within the document library and i just have huge list of files to select from
Hi,
I am unable to connect sharepoint online list to power BI
This is Error :
Details: "Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed:
OData Version: 3 and 4, Error: The remote server returned an error: (500) Internal Server Error. (500 INTERNAL SERVER ERROR)
OData Version: 4, Error: The remote server returned an error: (500) Internal Server Error. (500 INTERNAL SERVER ERROR)
OData Version: 3, Error: The remote server returned an error: (500) Internal Server Error. (500 INTERNAL SERVER ERROR)"Could anyone help me with this?
hey,
are you trying to connet to the site or the full URL? might sound daft but if you connect to just the site and not the full url you should be able to navigate to the list
Cheers
Al
hey !
I am giving this to sharepoint online list-
https://xyz-my.sharepoint.com/:f:/p/yashaswini_m
it's still showing this error :
Details: "Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed:
OData Version: 3 and 4, Error: The remote server returned an error: (500) Internal Server Error. (500 INTERNAL SERVER ERROR)
OData Version: 4, Error: The remote server returned an error: (500) Internal Server Error. (500 INTERNAL SERVER ERROR)
OData Version: 3, Error: The remote server returned an error: (500) Internal Server Error. (500 INTERNAL SERVER ERROR)"
Thank You Sharing that information Such a Helpful information.
How to Resolve Share Point Long Path Issue?
Microsoft Power BI: Read this simple explanation
