cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
avelio
Helper II
Helper II

Can't connect to Sharepoint subsites using PowerBI

Dears,

 

Currently i'm experiencing the following issue:

 

- My organization is keeping files on sharepoint.

- Im trying to use PBI to connect to our sharepoint files

- When i'm connecting to https://companyname.sharepoint.com/Site1/ it works just fine. The problem is that my data is stored into Subsites, so it goes something like this https://companyname.sharepoint.com/Site1/Subsite2.

- When i'm connectong to the subsite i get the following error: 

         Details: "Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed:
         OData Version: 3 and 4, Error: The remote server returned an error: (404) Not Found. (Not Found)
         OData Version: 4, Error: The remote server returned an error: (404) Not Found. (Not Found)
         OData Version: 3, Error: The remote server returned an error: (404) Not Found. (Not Found)"

- If i use GetData from PBI Web App i can connect to the Site (https://companyname.sharepoint.com/Site1) and browse from there our subsites and select the data.

 

Question: 

Why isnt this feature possible for PBI Desktop App?

Am i doing something wrong?

How can i connect the data from my subsites on sharepoint to powerbi desktop?

 

BR,

Andrei

1 ACCEPTED SOLUTION

Hi @avelio,

Navigate to the home page of your subsite in browser, then copy the URL which is like: https://XXXX.sharepoint.com/sites/patrick/team1/SitePages/Home.aspx, then exclude “/SitePages/Home.aspx” part from the URL and  enter URL like https://XXXX.sharepoint.com/sites/patrick/team1 in SharePoint connector in Power BI Desktop.
1.PNG

 

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

20 REPLIES 20
bhashwar
New Member

It still does not work when i try to connect to on prem sharepoint list like: http://intranet.contoso.com/subsite using get data from sharepoint lists. But it works when i use get data from web and enter the above url. Any ideas?

avelio
Helper II
Helper II

Dears,

 

I've been struggling in the past week to connect PowerBI Desktop to sharepoint online to extract some data from excels.

 

I've encountered the following problem:

 

  - When connecting to the sharepoint site https://company.sharepoint.com/Site cannot browse further my companys subsites in order to select the data that i need. I get the following screen:

 

Capture.JPG

 

In this screen if i select the subsites on the left i get nonsense data in the right. My data should be in these subsites in the form of excels.

 

If i try to connect directly to the subsite i get the following error:

 https://company.sharepoint.com/Site/Subsite

Details: "Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed:
OData Version: 3 and 4, Error: The remote server returned an error: (404) Not Found. (Not Found)
OData Version: 4, Error: The remote server returned an error: (404) Not Found. (Not Found)
OData Version: 3, Error: The remote server returned an error: (404) Not Found. (Not Found)"

 

If i type in the full path of my folder i get the following message:

 

 

Capture.JPG

 

The weird thing is that if i follow the same steps in app.powerbi.com i get a screen in which i can browse my subsites/folders and get my data.

 

I want to do this in PBI Desktop, not Web.

 

Any thoughts?

 

I'm mentioning that i have the latest version of PBI Version: 2.45.4704.722 64-bit (April 2017)

 

 

BR,

Andrei

 

Anonymous
Not applicable

Hi Andrei,  Power BI is actually now up to May, but it shouldn't impact what you are attempting.

 

In Power BI Desktop, use the "SharePoint Folder" as your get data connection type.  Provide it the URL without the "SubSite" portion.

 

 

The "Non-Sense" data you are seeing looks to be the SharePoint metadata and there should be a column that has the Binary in it.  You can select the binary in the value cell and it should open the document.

Hi @avelio,

Navigate to the home page of your subsite in browser, then copy the URL which is like: https://XXXX.sharepoint.com/sites/patrick/team1/SitePages/Home.aspx, then exclude “/SitePages/Home.aspx” part from the URL and  enter URL like https://XXXX.sharepoint.com/sites/patrick/team1 in SharePoint connector in Power BI Desktop.
1.PNG

 

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi, my PBI is in portuguese so I may mistranslate something, hope you understand it: when I follow the proposed solution I receive a message saying "The Resource is Forbidden" (or equivalent in English). Do you know if this is SharePoint permission related?

Anonymous
Not applicable

I've had this experience myself.  From my research you can only link your Gateway to the first site1 level and your data gateway should be able to reach down to those other levels.

I have no idea why SharePoint and Power BI are set up like that.  It certainly work though if you just link to that site 1 level.

Hi @Anonymous! I sure am glad im not the only one experiencing this issue.

The problem is that from PowerBI Desktop i cannot reach down other levels, only in app.powerbi.com and as a developer this doesnt help. 😞 

 

I'll create a support ticket and see what happens.

 

Thanks,

Andrei

Anonymous
Not applicable

I've found that if you set your desktop version to link to the full path, but your On-Premise Gateway on the online portal to link to the higher up Site1, they all work out fine.

Thanks @Anonymous but i'm not quite sure i understand what are the steps i need to take to get there.

Can you explain please as you would a dummy?:) 

Anonymous
Not applicable

When you are in the PBI Desktop, you'll need to specify the full path to draw down your data.  Your PBIX file will store that as your data source.

 

When you set up your On Premise Data Gateway in Power BI Web, you'll select your data source as Type: SharePoint and have a path of https://companyname.sharepoint.com/Site1/

 

When you publish your Power BI desktop to your Power BI Web, the data gateway is smart enough to recognise it has access to that full path, because you gave it access to the higher level.

@Anonymous Well that is the problem. In PBI Desktop if i specify the full path i get the error that i've posted. In PBI Desktop i can only specify https://companyname.sharepoint.com/Site1/ for it to work.

 

BR,

Andrei

Anonymous
Not applicable

Ok i've run some testing this morning, coincedently because a staff member also needed to do this today.  Here is what we did and it worked.

Select new data source and choose "SharePoint Folder" or "SharePoint List" depending on what task you are performing.  Choose https://companyname.sharepoint.com/Site1/ and then locate the files/lists as they appear in this list.  Everything should be in here for Site1.  On mine there is a "Folder Path" column which might help you filter down.

@Anonymous this is what i get if i try to reproduce your steps (sharepoint folder).

There is no possibility to drill down to subfolders from here. It just retrieves some nonsense data.

 

Does it have anything to do with the structure of the sharepoint folders/subfolders etc?

The weird thing is that in app.powerbi.com works just fine...

 

Capture.JPG

 

If i choose sharepoint list i get the following screen.

I can check/uncheck the subsites on the left side, but the information in it is nonsense, i should be able to browse to those subfolders and select a worksheet.

 

Capture.JPG

 

BR,

Andrei

Steve_S
Frequent Visitor

Ross73312's advice was perfect on this; your screenshot that you were referring to as "nonsense" data is all the files in all subsites as a giant flat list; scroll to the right and find the "folder Path" column, and then put a row filter on with a "contains" clause to filter down to the files in the specific subsite of interest.

 

Worked beautifully for me!

 

Thank you @Ross73312!

Steve_S
Frequent Visitor

Ross73312's advice was perfect on this; your screenshot that you were referring to as "nonsense" data is all the files in all subsites as a giant flat list; scroll to the right and find the "folder Path" column, and then put a row filter on with a "contains" clause to filter down to the files in the specific subsite of interest.

 

Worked beautifully for me!

 

Thank you @Ross73312!

Anonymous
Not applicable

It depends whether you are using Lists or Files.  Based on your original description, files will be what i imagine you need.

Have another look at your files list and there should be a column called "Folder Path".  You should be able to use this to find the files in your subfolders.  Essentually all of the files are there in that single list.

v-yuezhe-msft
Microsoft
Microsoft

Hi @avelio

Do you use the latest version of Power BI Desktop and which specific SharePoint connector do you use in Power BI Desktop? I use the SharePoint Folder and  SharePoint Online list connectors to connect to SharePoint Online subsite, everything works well.


Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Dear @v-yuezhe-msft, thank you for your reply!

 

My current version is Version: 2.45.4704.442 64-bit (April 2017).

I tried connecting using all 3 sharepoints data sources found in PowerBI (List,Online List and Folder). All of them work when i connect to the Site. If i try to connect to a Subsite i'm receiving the error: Details: "Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed:
OData Version: 3 and 4, Error: The remote server returned an error: (404) Not Found. (Not Found)
OData Version: 4, Error: The remote server returned an error: (404) Not Found. (Not Found)
OData Version: 3, Error: The remote server returned an error: (404) Not Found. (Not Found)".

 

My question to you is:

 

When your connecting to sharepoint to you type in the url https://Company.sharepoint.com/Site/Subsite or just https://company.sharepoint.com/Site/ and navigate from there to subsites?

 

Because if i connect to the site only, i dont have an option to navigate to subsites from there and if i try to connect to the subsites direectly i get the error i pasted before.

 

Any suggestions?

 

BR,

Andrei

Hi @avelio

I type in the URL: https://Company.sharepoint.com/Site/Subsite, in my scenario, the URL is https://xxxxx.sharepoint.com/sites/patrick/team1.

When you open your subsite URL in browser, does it work as expected? And does your subsite name contain slash or other specific characters?


Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello @v-yuezhe-msft,

 

When i type in the URL in browser it works as expected.

My subsite name is 'Reporting', it does not contain any specific characters.

 

The funny thing is that if i type in the exact url of the folder i want i get the following messageCapture.JPG

 

It specifically says to enter the site' root only 🙂 

 

BR,

Andrei

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors