Reply
logantbw
Helper I
Helper I
Partially syndicated - Outbound

Sharepoint folder connector returns blank page

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)

 

17 REPLIES 17
TomMartens
Super User
Super User

Syndicated - Outbound

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Syndicated - Outbound

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

Syndicated - Outbound

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Syndicated - Outbound

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.

 

 

Syndicated - Outbound

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Syndicated - Outbound

This may help

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Syndicated - Outbound

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

Syndicated - Outbound

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Syndicated - Outbound

There must be something with my english I swear.

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

IMG_0033.JPG

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

IMG_0032.JPG\

Sorry for phone pics.

Any ideas?

avatar user
Anonymous
Not applicable

Syndicated - Outbound

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

Syndicated - Outbound

@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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Syndicated - Outbound

@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')

Syndicated - Outbound

@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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Syndicated - Outbound

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

Syndicated - Outbound

Hello,

 

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

 

I'm getting no results, like you.

avatar user
Anonymous
Not applicable

Syndicated - Outbound

Hello, have you solved it?

Syndicated - Outbound

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

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)