Dyota's blog

Calling a Web API using Power Query

I had to make an API call through Power BI. Power BI actually has specialised syntax for dealing with tasks like this. Here are some functions that I learned about (I only know about these because Claude put it in the code that it generated for me).

API functions

Steps

In this API call, I had to do two steps:

Requesting for a token

let
    
    BaseUrl = "<url>,
    RelativePath = BaseUrl & "/some/path",
    
    // this is a record, that will be concatenated and escaped to form a URI string
    TokenFormData = Uri.BuildQueryString(
        [
            grant_type = "password",
            username = "<username>",
            password = "<password>
        ]
    ),

    // note here that the headers are passed in as a record
    // and that the form data is passed in as binary
    
    TokenResponse = Web.Contents(
        TokenUrl,
        [
            Headers = [
                #"Content-Type" = "application/x-www-form-urlencoded",
                #"Accept" = "application/json"
            ],
            Content = Text.ToBinary(TokenFormData)
        ]
    ),

    // the response in this case was in JSON
    TokenJson = Json.Document(TokenResponse),
    AccessToken = TokenJson[accessToken]
in
    AccessToken

Requesting data

Once the access token has been received, it can be used to authorise data requests.

Most of the syntax for the data request is the same, but note that:

  1. The header has the access token
  2. The body Content that is passed into the request is in JSON, not binary.
let
    RequestUrl = BaseUrl & "/api/some/path",
    
    // these are the parameters that the API needs
    // this is a record that is converted to JSON
    RequestBody = Json.FromValue(
        [
            startdate = TodayUtc,
            enddate = FourWeeksFromNow
        ]
    ),

    RequestHeaders = [
        #"Authorization" = "Bearer " & AccessToken,
        #"Accept" = "application/json",
        #"Content-Type" = "application/json"
    ],

    Response = Web.Contents(
        RequestUrl,
        [
            Headers = RequestHeaders,
            Content = RequestBody
        ]
    ),
    Data = Json.Document(Response)
in
    Response

#API #powerquery