Reply
Regular Visitor
Posts: 21
Registered: ‎04-03-2017
Accepted Solution

Can't connect to Sharepoint subsites using PowerBI

[ Edited ]

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


Accepted Solutions
Moderator
Posts: 4,660
Registered: ‎03-10-2016

Re: PBI Desktop connection to sharepoint online

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

View solution in original post


All Replies
Moderator
Posts: 4,660
Registered: ‎03-10-2016

Re: Can't connect to Sharepoint subsites using PowerBI

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

Regular Visitor
Posts: 21
Registered: ‎04-03-2017

Re: Can't connect to Sharepoint subsites using PowerBI

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

Moderator
Posts: 4,660
Registered: ‎03-10-2016

Re: Can't connect to Sharepoint subsites using PowerBI

[ Edited ]

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

Regular Visitor
Posts: 21
Registered: ‎04-03-2017

Re: Can't connect to Sharepoint subsites using PowerBI

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 Smiley Happy 

 

BR,

Andrei

Super Contributor
Posts: 885
Registered: ‎02-28-2017

Re: Can't connect to Sharepoint subsites using PowerBI

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.

Regular Visitor
Posts: 21
Registered: ‎04-03-2017

Re: Can't connect to Sharepoint subsites using PowerBI

Hi @Ross73312! 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. Smiley Sad 

 

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

 

Thanks,

Andrei

Super Contributor
Posts: 885
Registered: ‎02-28-2017

Re: Can't connect to Sharepoint subsites using PowerBI

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.

Regular Visitor
Posts: 21
Registered: ‎04-03-2017

Re: Can't connect to Sharepoint subsites using PowerBI

Thanks @Ross73312 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?Smiley Happy 

Super Contributor
Posts: 885
Registered: ‎02-28-2017

Re: Can't connect to Sharepoint subsites using PowerBI

[ Edited ]

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.