Sharepoint Folder

[admiralman]

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.

[infrassistnoc]

Thank You Sharing that information Such a Helpful information.

How to Resolve Share Point Long Path Issue?

Microsoft Power BI: Read this simple explanation

[YashaswiniMohan]

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?

[Alwyn1991]

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

[YashaswiniMohan]

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)"

[Alwyn1991]

when you go on home on your sharepoint site you want to use that URL rather than the url when you can see the list for example

https://my.sharepoint.com/sites/home/personal/list

you actually want 

https://my.sharepoint.com/sites/home

if that makes sense

[Anonymous]

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

[Alwyn1991]

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

[Anonymous]

In my case I reset the connection to sharepoint and then the new folders and files showed. Not obvious thing to need to do though, i guess it caches what it finds on first connection and doesn't refresh for a while.

[Alwyn1991]

Hey Neil,

it does cache the first time logon unless within Transform Data/Power Query editor you use Refresh preview within the query you want to refresh

[Alwyn1991]

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

[Anonymous]

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. 

[Alwyn1991]

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

[v-yulgu-msft]

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

[OnwardSolutions]

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 abou...

[aar0n]

@v-yulgu-msft

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"

[Anonymous]

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. 

[admiralman]

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 · Accepted solution]

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

[Amit_Dev]

Hi - 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.

[Anonymous]

Hi, I'm having the following issue: I'm trying to bring all the files from a specific folder in my sharepoint folder. I connect to my sharepoint folder, and I filter the folder path. When I do this from Power BI, I find the Path of that folder, but when I do it from Excel, I can't find it (I of course load all the data on the filter options). Its as if from excel, Power Query is not bringing completely what we have in our sharepoint folder (we have thousands of files). I even copied and pasted the same code from advanced editor.