Forum Discussion

newtonian1991's avatar
newtonian1991
Helper I
2 years ago
Solved

Connect API with expiring bearer token

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 = "[email protected]&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. 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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 = "[email protected]&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 And Power BI M Code (crossjoin.co.uk)

    Regards,

    Xiaoxin Sheng

2 Replies

  • newtonian1991's avatar
    newtonian1991
    Helper I
    2 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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 = "[email protected]&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 And Power BI M Code (crossjoin.co.uk)

      Regards,

      Xiaoxin Sheng