Connecting from power BI to rest API with Oath2 authentication

[Indrasena]

• Can someone provide a sample power bi project for connecting power Bi to rest API using Oath2 authorisation.

[WanderingBI]

I was also puzzled that the Web API connector in Power BI still does not support oAuth2.0. I wonder if anyone has developed a custom connector and made available to the public? 

Currently even Google Spreadsheets  third party tools for API ingestion offer support for oAuth 2.0, why not  Power BI? 

I have been googling for 2 days and the status is still the same:

• For stopping the frustration, Chris Webbs article is still the best ressource.
• If you have the access token, you can use Power Query to get the data, but (in most cases) you will not be able to request the access token within Power BI.

[Sarath_babu2]

I came across the same requirement and the solution in below link worked for me. Document was very clear and explained step by step in detail.

https://www.advaiya.com/generate-dynamic-token-and-get-data-from-custom-api-in-power-bi/

[Indrasena]

Please find the below snippet code and need some inputs for looping,triggering the web api and store each response as record. 

[kyrpav]

If you want to publish the report, there is no solution probably at the moment i have the same problem.

Service provides basic authentication.

In desktop you can easily in one query make the first call to the login to get the token and then pass the token to the second call as authorization.

body = "{"if it is required for the sign in or you 
            need to put somehow user and pass for sign in"}",
    
Data= Web.Contents("https://your.sign.in.api",
                            [Content=Text.ToBinary(body),
                             Headers=[#"Content-Type"="application/json"]]),  
DataRecord = Json.Document(Data) ,  
token=DataRecord[data],
    
Source = Json.Document(Web.Contents("https://your.rest.api", 
                                    [Headers=[#"type"="application_auth",
                                              #"Authorization"="Bearer "&token]])),

[Indrasena]

Is there any step by step guide like below, i am trying it for the first time as i dont have any idea on Oath2 and API authorisation,

https://www.myonlinetraininghub.com/connecting-to-an-oauth-api-like-paypal-with-power-query

It would be a great help if i can get some exact snippet M code.

[v-rzhou-msft]

Hi @Indrasena 

If you want to connect from Power BI to a REST API that uses OAuth2 authentication then you need to build a custom connector. You can find documentation on how to implement an OAuth2 flow in a custom connector here.

For reference: Connecting To REST APIs With OAuth2 Authentication In Power Query/Power BI

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GavinJolly]

I have tried building one but am not a developer and it is quite frustrating. Can someone just build one and publish it? I know there is an issue that PowerBI cannot deal with Secrets.

This person has built one for OuraCloud, can someone do the same for AzureAD.

• Building a custom connector for Power BI that supports OAuth2 to visualize my wellness data - Jussi ...
• OuraCloudConnector/OuraCloudConnector.pq at master · jussiroine/OuraCloudConnector · GitHub

Here was my stumbling path BEFORE I even got to developing, where once again, I realise I am not a developer. This was over 2 hours of just plain annoying documentation from Microsoft.

1. View Power Query SDK page - Need Visual Studio
2. Install Visual Studio - 2022 Community
3. FAIL Power Query SDK will not install
4. Research - PQ SDK only supports VS 2019
5. Install Visual Studio - 2019 Community
6. FAIL Cannot create project - Missing File
7. Research - Need any workflow - Apparently according to one page
8. Install Python extension - Smallest
9. FAIL - Cannot create Project - Something something Folder, Roslyn
10. Install another extension, .NET, C# and others
11.  SUCCESS: Can now create a new Connection project

Maybe the SDK page could say "Does not support 2022 - Install older VS version" and list what is needed to actually MAKE A **bleep** PROJECT.

SDK page: https://marketplace.visualstudio.com/items?itemName=Dakahn.PowerQuerySDK 

MS Docs: https://docs.microsoft.com/en-us/power-query/installingsdk