PowerBI linked to SharePoint Lists require a Personal Gateway?

[GrahamKnowles]

I've got a list created in SharePoint online which i want to use to display data into a PowerBI report.

I've connected the list into an excel spreadsheet through the Export to Excel option in the ribbon.  This spreadseet is then also saved into the SharePoint online site.

I've used the PowerBI desktop app to pick up this data from the spreadsheet and create a PowerBI file which is published to PowerBI.

I can now see and interact with the report within PowerBI, however i can't enable a scheduled refresh.  The only option i'm given is to create a Personal Gateway to allow scheduled refresh.  However i was under the impression (from this article https://support.powerbi.com/knowledgebase/articles/649846-power-bi-personal-gateway) that a gateway would not be required as the data is connected to an online source rather than on-prem data

Is anyone able to shed some light on this for me?  

Thanks

[kmcculley]

I found that my issue was for some reason PowerBI was logged in under my IT Manager's account. Oddly, when I would change user and type in my info it would still revert to his account. Weird right? Anyway, I signed out and restarted the app.. Then when I re-opened it where it still said SIGN IN on the top right corner, I followed the directions and it worked.. So.. try doing this logged out of PowerBI. 

[Gally]

Here's a blog post that I've written that describes an alternative use to the personal gateway that uses a 3rd party tool (AxioWorks SQList) to synch the SharePoint lists with normalised SQL Azure tables which then gives you the option of direct query. 

Since SQList updates SQL Azure as soon as SharePoint changes are made it is a very nice workaround for Direct Query

Power BI direct query workaround for SharePoint

[smpa01]

Not sure if this is resolved yet. I had similar problems in connecting share point data source to power bi. I could not connet from "Get Data" in PBI.

Instead, I did the following, My Computer >Map Network Drive>Connect to a Web site that you can use to store your document and pictures>Welcome to Add Network Location Wizard>Choose a custom network location>Internet or Network address (Please put the website address only and not the whole path to the list)>Type a name for this network location (Domain Name)> Finish

The share point files get downloaded under Network Location in My Computer. Now go back to PBI>Get Data> Excel>Network Locations>*Desired Excel File*

Everytime the *Desired Excel File* is amended in sharepoint, the update is reflected in the *Desired Excel File* under Network Locations and PBI refreshes it upon refresh in Edit Queries window.

[Rajiv]

I have done exactly the same thing as Graham, only difference is that I am using PBI online that comes with my Office 365 subscription. Is it not possible to link the PBI online report to a SharePoint online list?

Also, the refresh does not seem to be working as expected. I am using a stacked bar chart on PBI dashboard and after refresh all of the data is not reflected in the chart. But when an individual item is clicked and gets highlighted, the missing data shows up in the faded stacks. I hope I am making sense here!

Could this be because of any limits on the number of items you can have in a stacked chart? The chart looks fine in the Excel file imported from the list, but not on the PBI dashboard.

[Rcharles]

Similar to the previous post, I am trying to report on my Office 365 online Sharepoint site list.

I am able to create a link to my online sharepoint site. However, I see only document folders.  I do not see any reference to my sharepoint list. My hope is that I can graph the required metrics off my List data. What am I missing?

I do not want to extract the data to Excel and then push this back to the site for reporting...

An help will be appreciated.

[lapfama]

Hi,

Were you able to find a solution for this?

Entering the site's url, only shows 'Document Libraries', but not lists.

Thanks,

laura

[mcallough]

I am seeing the same error. I see my Document Libraries but no lists. 

[Ramakrishnan]

Samw problem faced by us in Power BI service

[erikskov]

Power BI can connect directly to a SharePoint O365 list.

To do so, select Get Data, 'More', Online Services, and SharePoint Online List.  Click Connect and enter your base site URL.  You will then be prompted to login using your Organization credentials.  Once logged in, you should now see a list of items whilch should include any lists you have created.  In my example, you can see the 'Custom List'.

[amandaeakins]

Can you please go into detail about how to move your page analytics into a custome report?

[aditirdatta]

Is this PowerBI online that you are referring to here OR the desktop version? Thanks!

[avelio]

@erikskov thanks for the tip.

My sharepoint URL is like this https://companyname.sharepoint.com/Site. If i type this url in i can connect just fine. The problem is that in this folder i have subsites and those are the folders that contain actual data (.xls .csv). If i type https://companyname.sharepoint.com/Site/Subite in the url which connects me to the sharepoint i get the follow 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)"

So, it's not enough to connect to the Sharepoint Site because thats not where my data is. It's stored in subsites and apparently i cant connect to those.

Moreover if i use PBI web app i can connect just fine to the site and then i can browse my sites/subsites and select which excel i want to use to retrieve data. This feature is not available on the desktop app.

To conclude:

- Using PBI desktop i can connect to sharepoint online site (https://companyname.sharepoint.com/Site) but cant drill down from there to subsites which is where my data is

- Connecting to the Subsites in my organization is not possible (error retrieved: 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)"

Anyone else experiencing this problem?

Is there a solution?

- Using PBI web app i can connect to my sharepoint site and from there browse my subsites to retrieve data.

[erikskov]

404 Usually refers to a File Not Found, so I'm guessing the URL is incorrect.  

I can access my O365 site and subsites from within Power BI Desktop directly.  To illustrate, I navigate to Site Contents and select Subsites:

Then, by selecting the 'Help Center' site and select site contents, get the full URL:  https://catsysdemo.sharepoint.com/sites/fusedeverikskov/helpcenter/_layouts/15/viewlsts.aspx?view=14

I paste that URL (minus the page info) into Power BI O365 Sharepoint List data connector, authenticate, and huzzah...there's my contents.

[ladymicrosoft]

I've wasted two hours today trying to be more productive at work by standing up a super simple power BI that connects to an excel file which I should be able to keep on my sharepoint folder.  but no, that's not possible at least having searched for two hours now.  I have an excel file and I don't know where I can put it where it will actually be of any use.  does anyone have a plan for this seeming insanely simple and every day need?

[GrahamKnowles]

Is the list on a specific page within the site?

If i try to connect to the top level site (https://tennant.sharepoint.com) I can't connect to lists on different pages of the site, instead I have to use a more specific address (https://tennant.sharepoint.com/page), this then gives me the ability to select lists to connect to

[Coelijoeli]

I've got the exact same problem/error when trying to configure a refresh plan with SharePoint.

There isn't even a "organization account" option when you need to verify a login type

[konstantinos]

You need to import/connect  the sharepoint list direct to PBI Desktop..The way you do it now it fetces the data from local excel file , that is why you need a Personal DMG..The connection that PBI desktop uses now to refresh is the connection to local excel, cannot pass the connection from sharepoint list that you store in local excel ..

[GrahamKnowles]

Hi Konstantinos

Thanks for the response.

I've tried connecting to the list directly from the Desktop app, but i'm getting an error connecting.

I'm assuming i should be using my Organizational Account to connect (have also tried Anonymous and Windows), but i get the following error:

DataSource.Error: SharePoint: Request failed: The remote server returned an error: (400) Bad Request.

Details: DataSourceKind=SharePoint

I'm guessing i've done something wrong but can't figure out what, any ideas?

Sorry for all the questions - i'm very new to PowerBI

[konstantinos]

@GrahamKnowles  Probably you have found the solution so far ( please mantion the name @) as we don't see new post on old topics..

Maybe there is an error in the way you write the path..(although sharepoint lists always had problems connecting )..Check this please and change the "Documents" with "List" or similar  file name with list name.

https://medium.com/@Konstantinos_Ioannou/onedrive-powerbi-desktop-use-valid-paths-to-import-data-sto...

@Coelijoeli In PBI desktop you choose basic and in PowerBI service oAuth2 for organisational accounts

.

[GrahamKnowles · Accepted solution]

Yes Konstantinos, i have this working now.

My problem was i was trying to connect to the exact link for the sharepoint list.  In order to correct i had to change this link to the general site address and once connected to the site in PBI Desktop select the sharepoint list from the list of available tables to load the data.

Thanks