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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
sherodkeen
Frequent Visitor

Bearer Token Expires before GET Request Completes

I have a couple of functions and a query that converts some data from an api to a table using power query. I am able to successfully get data for most endpoints in the API, but there are a few endpoints where the dataset is so larger that the Bearer Token expires before the GET request is complete. Because of this, the Dataflow refresh is failing.  The token is good for 30minutes. I've tried to figure out how to set the code so that it generates a new token, but it seems that it's just using the same token over and over and it eventually expires. 

 

Any ideas on making sure the queries are using a valid token? (The API does not have a "refresh_token" option). 

 

 

() => let
    token_url = tokenUrl,        
    body = "Client_id="&clientKey&"&Client_secret="&clientSecret&"&Grant_type="&grantType,
    // body = "Client_id="&clientKey&"&Client_secret="&clientSecret&"&Grant_type=refresh_token",
    Source = Json.Document(Web.Contents(token_url,
    [
        Headers = [#"Content-Type"="application/x-www-form-urlencoded"],
        Content = Text.ToBinary(body)
    ]
    )
    ),
    accesstoken = Source[access_token],
    token = try accesstoken otherwise Text.Combine({accesstoken[error]?, accesstoken[error_description]?, "Bad     authToken"}, " / ")
in
  token

 

 

 

(Offset as number, endpoint as text, ext as text)=>
let
    // token_url = tokenUrl,        
    // body = "Client_id="&clientKey&"&Client_secret="&clientSecret&"&Grant_type="&grantType,
    // Source = Json.Document(Web.Contents(token_url,
    // [
    //     Headers = [#"Content-Type"="application/x-www-form-urlencoded"],
    //     Content = Text.ToBinary(body)
    // ]
    // )
    // ),
    // token = Source[access_token],
    data = Json.Document(Web.Contents(baseUrl,
    [
        // Headers = [#"Authorization"="Bearer "&GetToken(),#"Content-Type"="application/json"],
        Headers = [#"Authorization"="Bearer "&GetToken(),#"Content-Type"="application/json"],
        RelativePath=relativePath&"/"&ext&"/"&endpoint,
        Query = [#"limit"=LimitOffset,#"offset"=Number.ToText(Offset)]
    ]
    )
    ),
    // #"Data" = data
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"

 

 

 

 

let
  Source = List.Generate(()=> [Result = fApi(0,endpoint,ext), offset=0, endpoint="studentSectionAssociations", ext="ed-fi"],
		                        each not (Table.IsEmpty([Result])),
                                each [Result = fApi([offset], [endpoint], [ext]), offset=[offset]+Number.FromText(LimitOffset), endpoint=[endpoint], ext=[ext]],
                                each [Result]),
  #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  #"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", {"Column1"}, {"Column1.1"}),
  #"Expanded Column1.1" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.1", {"id", "sectionReference", "studentReference", "beginDate", "endDate"}, {"id", "sectionReference", "studentReference", "beginDate", "endDate"}),
  #"Expanded sectionReference" = Table.ExpandRecordColumn(#"Expanded Column1.1", "sectionReference", {"localCourseCode", "schoolId", "schoolYear", "sectionIdentifier", "sessionName"}, {"localCourseCode", "schoolId", "schoolYear", "sectionIdentifier", "sessionName"}),
  #"Expanded studentReference" = Table.ExpandRecordColumn(#"Expanded sectionReference", "studentReference", {"studentUniqueId"}, {"studentUniqueId"}),
  #"Changed column type" = Table.TransformColumnTypes(#"Expanded studentReference", {{"id", type text}, {"localCourseCode", type text}, {"schoolId", Int64.Type}, {"schoolYear", Int64.Type}, {"sectionIdentifier", type text}, {"sessionName", type text}, {"studentUniqueId", type text}, {"beginDate", type date}, {"endDate", type date}})
in
  #"Changed column type"

 

7 REPLIES 7
Jan_Hob
Helper I
Helper I

@v-yalanwu-msft  I am looking for assistance with the same issue. Has anyone managed to solve this?

sherodkeen
Frequent Visitor

Anyone have any suggestions?

v-yalanwu-msft
Community Support
Community Support

Hi, @sherodkeen ;

You can check if these related post can is useful to you.

Solved: OAuth Refresh token has expired after 90 days - Microsoft Partner Community

Refresh the access token in Power BI embedded analytics | Microsoft Learn

Troubleshooting refresh scenarios - Power BI | Microsoft Learn

Access & Refresh Tokens | Online Help - Zoho CRM


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Unfortunatly, none of the links you provided help in this specific case. Is there anything I can modify in my code to ensure that it is using a new token for each api call? 

I'm attempting now to try and put the GetToken() function as a parameter in the table query in the List.Generation part of the function. Hoping this will retrigger the try for a token, which should be new each time the query is ran. 

I'm still struggling with this one. Looks like everytime the loop runs in the List.Generate(), the call to my custom GetToken() function is just regiving the loop the same token. I was hoping to get a new token on each loop so that I wouldn't run into the 30 min expiration that each token has. 

 

sherodkeen_0-1666126444159.png

 

let
  Source = List.Generate(()=> [Result = fApi(0,endpoint,ext,accesstoken), offset=0, endpoint="staffs", ext="ed-fi", accesstoken = GetToken()],
		                        each not (Table.IsEmpty([Result])),
                                each [Result = fApi([offset], [endpoint], [ext], [accesstoken]), offset=[offset]+Number.FromText(LimitOffset), endpoint=[endpoint], ext=[ext], accesstoken = GetToken()],
                                each [accesstoken])
in
Source

 

(Offset as number, endpoint as text, ext as text, accesstoken as text)=>
let
    data = Json.Document(Web.Contents(baseUrl,
    [
        Headers = [#"Authorization"="Bearer "&accesstoken,#"Content-Type"="application/json"],
        RelativePath=relativePath&"/"&ext&"/"&endpoint,
        Query = [#"limit"=LimitOffset,#"offset"=Number.ToText(Offset)]
    ]
    )
    ),
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"

 

() => let
    token_url = tokenUrl,        
    body = "Client_id="&clientKey&"&Client_secret="&clientSecret&"&Grant_type="&grantType,
    Source = Json.Document(Web.Contents(token_url,
    [
        Headers = [#"Content-Type"="application/x-www-form-urlencoded"],
        Content = Text.ToBinary(body)
    ]
    )
    )
in
  Source[access_token]

 

 

@v-yalanwu-msft Do you have any further ideas for help?

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors