Connect API with expiring bearer token

[newtonian1991]

Hi All, 

i have tried several different codes from this forum and am unabel to get API call working with a Bearer token that refreshes. 

e.g  i can reach teh API with the below code if i manually enter a bearer token but it expires every 60 minutes. 

let

apiUrl = "https://mxxxapi.enxxxsted.com/api/v1/personaldetails/GetCurrentPupilInfo/xxxxxx?encryptedpupilid=true",

token = "XYZ",

headers = [#"Authorization" = "Bearer " & token],

source = Json.Document(Web.Contents(apiUrl, [Headers=headers])) in source

i have created a query that pulls the bearer token. have also tried this an invoked function. 

let
           
            tokenUrl = "https://mxxxapi.engaxxxted.com/api/gettoken",
            tokenHeaders = [
                #"Content-Type" = "application/x-www-form-urlencoded"
            ],
            tokenRequestBody = "username=gxxxxs@mlxxxxxxx.au&password=xxxxxxxxx",
            tokenResponse = Json.Document(Web.Contents(tokenUrl, [Headers=tokenHeaders, Content=Text.ToBinary(tokenRequestBody)])),
            token = tokenResponse[access_token]
        in
            token

 and have then tried to reference the token gathered above in the call for my information. 

let
   
    GetBearerToken = GetToken,

    apiUrl = "https://mxxxxxpi.engxxxosted.com/api/v1/personaldetails/GetCurrentPupilInfo/CL1-xxx?encryptedpupilid=true", 
    headers = [#"Authorization" = "Bearer " & GetBearerToken],
    source = Json.Document(Web.Contents(apiUrl, [Headers=headers]))
in
    source

And i get the following error

Formula.Firewall: Query 'API Call' (step 'source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination

As mentioned i ahve tried several methods found online to no avail. Any help woudl be greatly appreciated. 

[newtonian1991]

Please note i have solved this by selecting ignore privacy level settings in the options. 

[Anonymous · Accepted solution]

Hi @newtonian1991,

I'd like to suggest move the get token steps above the processing instead of reference cross queries, these tokens will update every time the query table refreshes to avoid the expired issues.

let
    rootURL = "https://mxxxapi.engaxxxted.com/api",
    tokenUrl = "/gettoken",
    tokenHeaders = [
        #"Content-Type" = "application/x-www-form-urlencoded"
    ],
    tokenRequestBody = "username=gxxxxs@mlxxxxxxx.au&password=xxxxxxxxx",
    tokenResponse = Json.Document(
        Web.Contents(
            rootURL, [
                Headers = tokenHeaders,
                RelativePath = tokenUrl,
                Content = Text.ToBinary(tokenRequestBody)
            ]
        )
    ),
    token = tokenResponse[access_token],
    apiUrl = "/v1/personaldetails/GetCurrentPupilInfo/CL1-xxx",
    headers = [#"Authorization" = "Bearer " & token],
    source = Json.Document(
        Web.Contents(rootURL, [
            Headers = headers,
            RelativePath = apiUrl,
            Query = [
                encryptedpupilid = "true"
            ]
        ])
    )
in
    source

In addition, you can also take a look at the following blog about M query web.contents option parameter usage if it helps:

Chris Webb's BI Blog: Using The RelativePath And Query Options With Web.Contents() In Power Query An...

Regards,

Xiaoxin Sheng