Accessing data using APIs

[Markzolotoy]

I am working on my first Power BI report. I have successfullt published it. I have aslo installed a gateway and configured it. The report shows a few records from a single sql table. The server is installed locally. So far, everything works. Now I want to get real data into my report using an api request. These APIs are token protected, so in order to use them I need to obtain a token first and then attach it to every api call. Is there a strategy for that in Power BI?

Thanks

[PhilipTreacy]

Hi @Markzolotoy 

You need to check if the token is valid. There's a couple of ways to do this.  Either by checking the response when requesting API data, if you get an error message saying the token is invalid then request a new one.

Or some API's tell you when the token expires. If that period has passed then request a new one.

The token request is just another query that you call as needed.

Regards

Phil

[Markzolotoy]

@PhilipTreacy What type of query would I use to get data using API with tokens?

Also my token API is a POST.

[PhilipTreacy]

Hi @Markzolotoy 

What API are you referring to?

How you proceed will depend on the API but you would use Power Query to issue the requests for data.

Exactly how you do this will depned on the API and how it requires authorisation for the data requests.

You said that the API requests are token protected, but is it a basic authorisation process where you get a token and then supply it in a HTTP header or as part of the URL when requesting data?

Or is it a more complicated OAUTH process?

You'll have to refer to the API doumentation to find this out.  If you can tell me the API I'll check.

regards

Phil

---

If I answered your question please mark my post as the solution.

If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[Markzolotoy]

@PhilipTreacy The APIs are a part of an on-prem application that are going to be accessed via a gateway I presume. Theses APIs are Web APIs written in .NET. currently the authorization works like this. First, a call is placed to get a token. The call must be using https protocol and  it has a user name and a password. All other have a bearer token in their headers. It is a pretty standard stuff.

[Markzolotoy]

Just bumping up. Can anyone provide a tutoral of how to access on-prem data using token protected APIs? 

Thanks

[PhilipTreacy · Accepted solution]

Hi @Markzolotoy 

You can check this article for an explanation of an API that requires token generation and then data requests using that token

Connecting to an OAUTH API

The code for the process is shown below. Obviously you will need to modify it to enter your own details e.g. URL, ClientID (username), Secret(password) etc.

let
    // Get the API Token
    api_url = "https://API_URL/",
    token_path = "TOKEN_PATH",
    ClientID = "xxxxxxxx",
    Secret = "xxxxxxxx",
    
    EncodedCredentials = "Basic " & Binary.ToText(Text.ToBinary(ClientID & ":" & Secret), BinaryEncoding.Base64),
    
    Token_Response  = Json.Document(Web.Contents(api_url,
    [ 
      RelativePath = token_path,
      Headers = [#"Content-Type"="application/x-www-form-urlencoded",#"Authorization"=EncodedCredentials],
      Content=Text.ToBinary("grant_type=client_credentials")
    ]
    )
    ),
   
    // Get the token from the API response 
    token = Token_Response[access_token],

    // Query the API for data
    path = "DATA_PATH",  
    params = "", // Query parameters

    data= Json.Document(Web.Contents(api_url,
   [ 
     RelativePath = path,
     Query = [params=params],
     Headers = [#"Authorization"="Bearer "&token,#"Content-Type"="application/json"]
   ]
   )
   )

in
    data

As I am not familiar with your API I don't know how much modification the above code will require. 

Things to note about the above code:

1. Expects JSON as the response

2. Is built with the expectation that query parameters will be required.  The params string is where you would include things like paging or throttling e.g. https://api_url/?page=10&limit=100 .

Hope this goes towards helping you out.

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[Markzolotoy]

@PhilipTreacy I am going to look into the link you are provided. About your code. There is one more piece that is needed to make it work. It's the refreshing of a token. In reality a token periodically expires and needs to be refreshed. On-prem application uses interceptors to handle that. How does Power BI  handle this problem?

Thanks