Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
newtonian1991
Frequent Visitor

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 = "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. 

1 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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
newtonian1991
Frequent Visitor

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

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.