Easiest way of connecting Power BI to a REST API with Oath2 authentication

[Bend-IT]

Hi, I'm having a hard time seeing the forest for the trees as there's so much information available (but with seemingly different use cases). I'm hoping I can get some guidance and pointers in the right direction.

The case is as follows:

We're trying to build a reporting solution that uses Power BI to visualize data from a REST API of a third party. The end user will use the Power BI service with a Pro license. The end user can be seen as a 'regional manager'. The reporting solution will integrate data from multiple stores, each with their own API-endpoint.

The API of the third party supports the implicit and authorization code grant types. 

My understanding is that Power BI does not support a direct connection to the API, with token refreshes and such. So the 'logic' for handling authorization and refresh tokens and such needs to be hosted elsewhere.

I think the first that needs to happen is that we need to get the client ID and client secret of each store. And after that something with a 'Azure AD native app' or a Power BI app? I'm totally lost as to how this is (most easily) achieved.

Any help would be greatly appreciated!

[v-juanli-msft · Accepted solution]

Hi @Bend-IT 

A easy way is to create M code in Advanced editor as below:

MyConnector.Raw = (_url as text) as binary =>
let
    apiKey = Extension.CurrentCredential()[Key],
    headers = [

        #"x-APIKey" = apiKey,
        Accept = "application/vnd.api+json",
        #"Content-Type" = "application/json"
    ],
    request = Web.Contents(_url, [ Headers = headers, ManualCredentials = true ])
in
    request

Reference how to write the code to connect to rest api data source:

https://docs.microsoft.com/en-us/power-query/handlingauthentication

http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/

https://community.powerbi.com/t5/Power-Query/Dataset-Refresh-REST-API-amp-JSON-with-required-Param-i...

Besides, you could use connector in Power BI Desktop, like ODBC connector, reference here.

To connect to the source with dynamic API key,

you could create query parameter (there are different values for a parameter, each value represents a different key),

and use it the code of Advanced editor (in Edit queries).

Dynamic API parameters in Power BI

https://community.powerbi.com/t5/Desktop/How-to-pass-a-dynamic-date-parameter-in-the-json-api-in-pow...

https://community.powerbi.com/t5/Power-Query/Rest-API-With-User-Selected-Parameters-As-Data-Source/m...

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Bend-IT]

Sorry for the late reply, it's been a bit hectic. I will try these approaches (and report back here), they look very doable, thanks!