Forum Discussion
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.
Regards,
Yuliana Gu
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 GuHi - I'm sure this has already been posted. But in case it hasn't, to target a Sharepoint folder, you need to replace "Sharepoint.Files…" with "Sharepoint.Contents…"
once you do this you can then leverage Sharepoints file hierarchy. Expand the "Shared Documents" table and then everything from there should be intuitive.
hope this helps and apologies again if this is simply a repeat of the above.
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)
