Cannot schedule or demand a refresh for SharePoint Online List in Power BI

[jwadams]

I have a power Bi file that was created in Power BI desktop and published. The data sources are a sharepoint online list and a local excel spreadsheet. When I go into Power BI I can see the report and data sets. However, I cannot get the schedule refresh to work.  It requires me to install the Personal Gateway even though it says you don't need on for any source connected through GET DATA in desktop.  So I install the gateway and configure.  No matter what I do I cannot get the SharePoint list credentials to work.  The excel spreadsheet connects but not the SharePoint Online List.

I can connect in Power BI Desktop  and refresh the data there.  Once I pulblish to the service, it seems to want to connect to the SharePoint List directly.  I thought I would create a pbix file through Desktop and store it on my laptop. Then the Power BI service would refresh the data from pbix file without going directly to the O365 cloud.

Any help would be appreciated.  Thanks  -JA

[kdurbin]

I was able to stop this behavior by changing the data source in PBI Desktop to an encrypted connection.  I opened my dashboard in PBI Desktop, opened the Data Source Settings menu, clicked Change Source and updated the URL to the HTTPS.  Once I re-published, I was able to authenticate and schedule a refresh with no problem.

[Greg_Deckler]

That makes total sense actually, God forbid if you actually do OAuth over non-TLS connection!

[neds]

So not sure why this is happening, but when i update the credentials online, the authorization method seems to default to Anonymous even when I try to chang it to oAuth2.  It will not save the credential request, and though it seems i can sign in correctly on oAuth, it seems to not save the new credentials and reverts back to Anonymous every time.

[neds]

I am having the exact same issue.  Everything works in Desktop, but get the following error trying to refresh online.

[Greg_Deckler]

@jwadams - I suspect that the gateway is required because you are using a using a local Excel file. I do not believe that a gateway is technically required and the Service will just update a SharePoint Online list directly. You should be using OAuth in the service to connect to it. Historically, there have been periods where SharePoint Online lists don't work for some reason or lose their mind and forget their credentials repeatedly.

When you publish a Desktop file to the Service, it creates a data set and essentially imports the connection source information from the PBIX, but the PBIX doesn't really *exist* in the Service as a PBIX, it is a "data set". Scheduled refresh then does its own thing in the Service whether it uses the Service to refresh cloud-based data sources or a gateway to refresh local data sources. 

[jwadams]

I read about using the OAuth option but when I select OAuth2 from the list,  it says "The OAuth authentication method isn't supported for this data source. Contact your admin."

When I try to refresh now, I get the following which I think is because the SharePoint credentials aren't set correctly.

Something went wrong.

An error occurred while processing the data in the dataset.

Data source type

Processing errorThe credential stored in the cloud is missing.

Cluster URIWABI-WEST-US-redirect.analysis.windows.net

Activity IDc36f048a-3935-41fb-9e22-1f37bbde382b

Request ID6e926297-3d47-7da1-02d6-e69bb4a6e838

Time2016-03-14 21:21:17Z

Between this and the fact the drill doesn't work on the dashboard, it may be that Power BI is not quite ready for how we would like to use it.  I'm hoping not but seems like using O365 with anything seems to be problematic.

Thanks.  -JA

[Greg_Deckler]

@jwadams - OK, I just tested this. I created a new PBIX file in Desktop and connected to a SharePoint Online list. I used Organizational Account in Desktop to connect. I then published to Power BI Service. In Power BI Service, clicked the data set's ellipses and whent to Schedule Refresh. It told me that my data source credentials were invalid. I hit "Edit credentials" and chose OAuth2 and it made me log in to O365 account (work/organizational account) and everything seems good to go. 

I'm not sure why you would receive that OAuth2 is unsupported unless you aren't really connecting to SharePoint Online but rather SharePoint on-premises? Can't think of what else might be going on there.

[Greg_Deckler]

That's very strange. OAuth2 is the right protocol for SharePoint Online, I'm not sure why you would be getting an error that it is not supported. I have used it successfully in the past although I am going to give it another try just to be sure.