cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

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

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.

View solution in original post

2 REPLIES 2
Highlighted
Community Support
Community Support

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

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.

View solution in original post

Highlighted
Frequent Visitor

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

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!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Come join us today! Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.