Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

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

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,

Frequent Visitor

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.

Solution Sage
Solution Sage

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

Updated – Get SharePoint List Data … Fast – Hoosier BI



Microsoft Employee
Frequent Visitor

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.

Super User
Super User

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:






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:



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 

Community Support
Community Support

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...


Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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:

Super User
Super User

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?

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?

Helpful resources

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors