Sharepoint folder connector returns blank page

[logantbw]

Hello, everyone.

Is there any option, I should turn on or off on sharepoint site, to retrieve folder contents via "Sharepoint folder" connector.

Currently I load all files via seperate functions written manually, but it doesn't update in Power BI add-in in SSRS. I am the admin of sharepoint site where all files located, so I think it's not due to a lack of access.

Also, tried to turn on/off web view of excel files. (...?Web=1)

[TomMartens]

Hey @logantbw,

first you have to point your queries to your Excel files in the sharepoint folder.

Determine the web path of the Excel files and remove the ?web=1

Then you also have to set a refresh schedule, following the instructions of this article:

https://docs.microsoft.com/en-us/power-bi/report-server/scheduled-refresh

Hopefully, this provides what you are looking for.

Regards,

Tom

[logantbw]

Hello @TomMartens 

Targeting excel files seperatly is not an option. Data will be being added every month.

"Determine the web path of the Excel files and remove the ?web=1" - There is an option in sharepoint that removes ?web=1 from folder contents. Told about it in initial post. This didn't help

[Daviejoe]

Hi @logantbw 

I don't think @TomMartens meant you would target the excel files directly, you'd point your query towards the folder where they are stored, so once you've achieved this you will pull in any new data that's added to the existing files or, if you're appending new files, they'd be appended to your data set.

Source = SharePoint.Files("https://companyname.sharepoint.com/sites/O365_FolderLocation", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "https://companyname.sharepoint.com/sites/O365_folderlocation/Shared Documents/etc")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content", "Name"})

So something like the above

[logantbw]

Thanks for answer @Daviejoe 

"Source = SharePoint.Files("https://companyname.sharepoint.com/sites/O365_FolderLocation", [ApiVersion = 15])," - this default connection returns me just blank table. But I do have files in folder, I even managed to get them through api requests.

Cannot upload an image to show.

[Daviejoe]

Hi @logantbw my apologies, this was an example of how the string would look, it wouldn't be exactly how yours would look.

[Daviejoe]

This may help

https://powerbi.tips/2016/09/loading-excel-files-from-sharepoint/

[logantbw]

Result of my query looks like this. And there is nothing only message "This table is empty"

[Daviejoe]

I'll step through how I connected to the data on a SharePoint folder

https://enteryourcompanynamehere.sharepoint.com/sites/enteryoursharepointfolderhere

The folder should be your overall SharePoint folder and not the specific location where the files are.

You should now get the Power Query editor appear, Select Transform Data.

The far right hand column should be Folder Path, then select the actual folder where your files are stored from this drop down list.

Then go to the far left hand side, Content, then select the excel sheets you require.

You can either load them straight as they are or you can go through the transformations on the 1st file that will be applied to the other files.

Let me know if this works for you, I'll try to help further if required.

[logantbw]

There must be something with my english I swear.

Here is connecting through http://sitename/sites/mysite

And this one is in example @Daviejoe provided (http://sitename/sites/mysite/folder)

\

Sorry for phone pics.

Any ideas?

[PauleLiub]

Hi, I get the same error, how did you resolve this please?

[Daviejoe]

@logantbw Nothing wrong with your english, perhaps I'm misunderstanding.  Where are you from?

I'd try dropping off the last \ and the folder name after it

[logantbw]

@Daviejoe Russia. 

I tried already didn't help. By the way Power Query sometimes removes or adds symbols for queries by itself.

But I can access files with same credentials by api requests like http://sitename/sites/mysite/_api/web/GetFolderByServerRelativeUrl('url')

[Daviejoe]

@logantbw Russia, cool 👍

I'm not really sure what to advise now.

I'd try to play around with the Sharepoint string, start with the longest string and if that doesn't work, work backwards removing one piece at a time to see if the query provides you with returns in the query.

I'd be happy to do a video chat with you at some point if you felt it would help.

Thanks

Scottish Dave

[logantbw]

I'd try to play around with the Sharepoint string, start with the longest string and if that doesn't work, work backwards removing one piece at a time to see if the query provides you with returns in the query. - also tried already 🙂

Think it has to be something with sharepoint site settings, I've spent like two days before posting this to community forum, last piece of hope I would say.

I'll try to reach sharepoint support and come back if they provide me a solution.

Thanks for frequent feedback

[fiamerico]

Hello,

I know this thread is old, but you solved the issue?

I'm getting no results, like you.

[PauleLiub]

Hello, have you solved it?

[Daviejoe]

@logantbw No worries, feel free to click the Kudos/Thumbs Up on a few of my replies if you don't mind 😉