topic Re: Connecting from power BI to rest API with Oath2 authentication in Developer

Connecting from power BI to rest API with Oath2 authentication

Indrasena — Sun, 05 Dec 2021 22:04:34 GMT

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

Re: Connecting from power BI to rest API with Oath2 authentication

kyrpav — Mon, 06 Dec 2021 08:25:03 GMT

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]])),

Re: Connecting from power BI to rest API with Oath2 authentication

Indrasena — Mon, 06 Dec 2021 08:44:28 GMT

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.

Re: Connecting from power BI to rest API with Oath2 authentication

Anonymous — Tue, 14 Dec 2021 10:02:07 GMT

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.

Re: Connecting from power BI to rest API with Oath2 authentication

Indrasena — Sun, 19 Dec 2021 18:13:54 GMT

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

Re: Connecting from power BI to rest API with Oath2 authentication

GavinJolly — Sun, 19 Dec 2021 19:39:57 GMT

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.

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.

View Power Query SDK page - Need Visual Studio
Install Visual Studio - 2022 Community
FAIL Power Query SDK will not install
Research - PQ SDK only supports VS 2019
Install Visual Studio - 2019 Community
FAIL Cannot create project - Missing File
Research - Need any workflow - Apparently according to one page
Install Python extension - Smallest
FAIL - Cannot create Project - Something something Folder, Roslyn
Install another extension, .NET, C# and others
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

Re: Connecting from power BI to rest API with Oath2 authentication

Sarath_babu2 — Fri, 16 Feb 2024 16:27:48 GMT

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/

Re: Connecting from power BI to rest API with Oath2 authentication

WanderingBI — Sat, 29 Jun 2024 04:19:34 GMT

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.