Table of Contents
In this blog, we will show how to authenticate APIs that use OAuth 2.0 and set it up in a way that scheduled refreshes from Power BI service will not fail at all.
It’s important to know the way APIs work may vary and you may not be able to apply 100% what we show in this article but the basics for API OAuth connections are the same in Power BI regardless of how the API service works.
OAuth 2.0 connections in Power BI usually require 2 steps:
- Getting an access token from an authentication endpoint
- Calling the API endpoint and make reference to the access token obtained in point #1.
How we achieve points #1 and #2 will depend on how the API works but it’s important that if you plan to publish the report to the Power BI service, all the code is written in the same query – i.e., do not call the access token from a separate query or the refresh will fail.
Step 1. Get access token
Start a blank query and call the authentication enpoint using the Json.Document function. Make sure to indicate the relative path and headers.
let
response = Json.Document(Web.Contents("https://pgi. apiservice.com/auth", [
RelativePath="token",
Headers=[#"Content-Type"="application/x-www-form-urlencoded"], Content=Text.ToBinary("grant_type=client_credentials&client_id=xyz&client_secret=abc&scope=all")])),
access_token = response[access_token]
in
access_token
For above code, the step “response” will include the access token, and the attribute “access_token” is what gives us the code we need to use in the following steps
Step 2. Call API service
Let’s add a new step in the query that will call the API endpoint we’re interested in – in this case, Clients. We will use again the Json.Document function, and separate the Relative Path, Query and Headers.
Make sure to include query parameters in the Power Query section and not as part of the URL, otherwise refresh in Power BI may fail.
Note how the header includes a reference to the access_token step calculated in step #1
API_Data= Json.Document(Web.Contents("https://pgi. apiservice.com/api", [
RelativePath="Clients",
Query=[page_no="1",page_size="100"],
Headers=[#"Content-Type"="application/json", Authorization="Bearer "&access_token]]))
Final code should look like this:
Conclusion
By using some M code and a couple of steps in Power Query you can connect to APIs that authenticate through OAuth 2.0. Contact us if you have any questions or if you want to know how we can help you create amazing automations with your internal systems and third party apps that have APIs available!
