Sharepoint List API connection - published data source credentials - oath2 not an option

[Jojo197]

I am connecting to a Sharepoint List using the api to improve refresh speed, but am unable to set up OAuth2 authentication in the data source credentials after publishing and hence cannot set up a scheduled refresh.

I am using a parameter for the relative path as seen below, and invoking a custom function to combine this query for 4 different locations on sharepoint (first blanked out part of Parameter current value)

Link Parameter:

This works fine in the desktop editor, but after publishing to the online service I was not able to configure my data source credential properly. Normally I have been using OAuth2 authentication and Organisational privacy, however when I try to set this up, it only shows anonymous and basic authentication as seen below, which will not work.

Is anyone able to help me understand why OAuth2 is not an option and what I can do to set it up properly?

Thanks in advance,

 

[Jojo197]

Some additional Info,

If I use Sharepoint List instead of Online List, the edit credentials window looks exactly the same but gives the oauth2 option which is what I want. However this method is extremely slow for the size of my data so it is preferable if I can get the Online List to work.

[ppm1]

Please see this article that shows how to get list data fast with scheduled refresh.

Updated – Get SharePoint List Data … Fast – Hoosier BI

Pat

[Jojo197]

Hi Pat,

The method in that article is what I have been using currently (with a few slight differences) but the same issues are there of oauth2 authentication not being an option when I publish to power BI, which means I cannot authenticate the data source.

[Daryl-Lynch-Bzy]

Hi - seems odd.  It don't recognise the screenshot.  I would expect the following from Power BI Desktop:

You could adding a Dataflow instead using the following steps:

[Jojo197]

Hi, I've been trying to get this working but when I select my on premises gateway for the data gateway section and try to create, the authentication window pops up for 1 second then closes and gives the following error:

[Jojo197]

Hi, the screenshot is from after I publish to Power BI online and try to set up the data credentials for automatic refresh. I get the same connections as you shared when in desktop, the only problem I have is after publishing.

I will try setting up a dataflow soon and also a custom connector as Xiaoxin suggested 

[v-shex-msft]

HI @Jojo197,

Perhaps you can take look at the following blog about create custom connector to use OAuth credentials with rest API:

Chris Webb's BI Blog: Connecting To REST APIs With OAuth2 Authentication In Power Query/Power BI Chr...

Regards,

Xiaoxin Sheng

[Jojo197]

Hi Xiaoxin, I have been trying this for a while but cannot get it to work. I am creating a connector through the make.powerapps but I am not sure what urls to use for the tokens as everything I have tried has failed the test connection.
I have tried with what the following sites have suggested but has not worked:

https://support.microsoft.com/en-us/topic/verifying-oauth-configuration-for-custom-connectors-d733fd...

https://learn.microsoft.com/en-us/answers/questions/353564/authorization-and-token-end-points-for-sh...

[Daryl-Lynch-Bzy]

Hi @Jojo197 - Could you please use Implemation="2.0" version of the Power Query SharePoint connector - Power Query SharePoint Online list connector - Power Query | Microsoft Learn?   Have you considered separating the SharePoint List read from any transformation  by using Dataflow to extract data and then another to transform it?

[Jojo197]

Thanks for the response. I tried using the online list connector with just a source and navigation step but I am getting the same issue except only the anonymous option is showing up now.

 What do you mean by using Dataflow to extract data and another to transform?