Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jojo197
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)

Query.png

Link Parameter:

query3.png

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.

query2.png

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,

10 REPLIES 10
Jojo197
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.

ppm1
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

 

Pat

Microsoft Employee
Jojo197
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.

Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

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

DarylLynchBzy_3-1670232065185.png


You could adding a Dataflow instead using the following steps:

DarylLynchBzy_0-1670231696140.png

DarylLynchBzy_1-1670231719395.png

 

DarylLynchBzy_2-1670231840407.png

 

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_0-1670462495028.png

 

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

Regards,

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:

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
Resident Rockstar
Resident Rockstar

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.

Query.png

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.