How to refresh access token with custom connector (OAuth2)?

[ArvindBhatt]

I have created a custom connector that calls REST API, API requires access token that gets saved intially when user logins.
my question is how to refresh the access token
does powerBI Automatically refreshes the access token once it expires or we have to do it manually before calling the API.

I checked power bi does not automatically refreshes the token then i tried doing it before the api call but in M code there is no way i could find to save the new access token.

[v-hashadapu]

Hi @ArvindBhatt , Hope your issue is solved. If it is, please consider marking the answer 'Accept as solution', so others with similar issues may find it easily. If it isn't, please share the details.
Thank you.

[v-hashadapu]

Hi @ArvindBhatt , Hope your issue is solved. If it is, please consider marking the answer 'Accept as solution', so others with similar issues may find it easily. If it isn't, please share the details. Thank you.

[v-hashadapu]

Hi @ArvindBhatt , Hope your issue is solved. If it is, please consider marking it 'Accept as solution', so others with similar issues may find it easily. If it isn't, please share the details. Thank you.

[ArvindBhatt]

Hi @v-hashadapu @rajendraongole1 
I tried generating new access token from the refresh token but issue with that is i am not able to save the new access token as Credentials so that i can use the new access token instead generating it everytime.
There is no method in power query which saves the new credentials.
Also one more question when the access token or refresh token gets expired it does not ask me for re-authentication. powerbi keeps on using old token.

shared GetValidToken = () =>
    let
        // Fetch the stored credentials from Power BI
        tokens = Extension.CurrentCredential(),
        accessToken = tokens[access_token],
        refreshToken = tokens[refresh_token],
        expiresAtText = tokens[expires],
        // expiration time (text format)
        // Convert expiration time from Text to DateTime
        expiresAt = DateTime.FromText(expiresAtText),
        // Get the current time
        currentTime = DateTime.LocalNow(),
        // Check if the token has expired
        isTokenExpired = expiresAt <= currentTime,

        // If expired, refresh the token
        newTokens =
            if isTokenExpired then
                let
                    // Call the refresh token method to get a new access token
                    refreshedTokens = TokenMethod("refresh_token", refreshToken),
                    // "dan" is placeholder for code_verifier
                    newAccessToken = refreshedTokens[access_token],
                    // New access token
                    newRefreshToken = refreshedTokens[refresh_token],
                    // New refresh token
                    newExpiresAt = refreshedTokens[expires]
                    // New expiration time
                    // Store the new tokens in Power BI credentials
                in
                    Extension.CurrentCredential(
                        [
                            access_token = newAccessToken,
                            refresh_token = newRefreshToken,
                            expires = newExpiresAt
                        ]
                    )
            else
                tokens,
        // Return the existing tokens if not expired
        // Return the valid access token
        validAccessToken = newTokens[access_token]
    in
        validAccessToken;

[v-hashadapu]

Hi @ArvindBhatt , thank you for reaching out to the Microsoft Fabric Community Forum.

@rajendraongole1 provided the correct answer. Please try it. If you still have any queries related to this issue or the answer doesn't work, please share the details.

If it does, please consider accepting his answer, so others with similar queries can find it easily.
Thank you.

[rajendraongole1]

Hi @ArvindBhatt  - You can store the refreshed tokens in a static variable (as shown in your GetValidToken function) during the session.

shared GetValidToken = () =>
let
tokens = Extension.CurrentCredential(),
accessToken = tokens[access_token],
refreshToken = tokens[refresh_token],
expiresAtText = tokens[expires],
expiresAt = DateTime.FromText(expiresAtText),
currentTime = DateTime.LocalNow(),
isTokenExpired = expiresAt <= currentTime,

refreshedTokens =
if isTokenExpired then
TokenMethod("refresh_token", refreshToken)
else
tokens,

validAccessToken = refreshedTokens[access_token]
in
validAccessToken;

When tokens expire, you can force reauthentication by returning an HTTP 401 error in your connector.

if isTokenExpired then
error Error.Record("TokenExpired", "Access token has expired. Please reauthenticate.")

[ArvindBhatt]

Hi @rajendraongole1 
in above code when token is expired and we get new access token but we are not saving it anywhere as there is no function i think to save the credentials.
Extension.CurretCredentials() function will return old access token.


and about this

if isTokenExpired then
error Error.Record("TokenExpired", "Access token has expired. Please reauthenticate.")

how user will re-athenticate, will it got to data source setting clear the credential and then again reconnect the connector? 
or this is there any other way. (I'm new to the power bi things sorry if this is a basic question)

[v-hashadapu]

Hi @ArvindBhatt , thank you for reaching out to the Microsoft Fabric Community Forum.

1. Continue using the GetValidToken function to check for token expiry and refresh it when necessary.
2. Use a static variable within your Power Query function to store the new tokens. Static variables persist across the lifetime of the query session.

Try this:
shared GetValidToken = () =>

    let

        // Define a static variable to store tokens

        cachedTokens = if IsNull(cachedTokens) then null else cachedTokens,

        // Check if cached tokens exist and are valid

        tokens =

            if IsNull(cachedTokens) or DateTime.FromText(cachedTokens[expires]) <= DateTime.LocalNow() then

                let

                    // Fetch new tokens using the refresh token

                    refreshedTokens = TokenMethod("refresh_token", cachedTokens[refresh_token]),

                    // Update the cached tokens

                    newCachedTokens = [

                        access_token = refreshedTokens[access_token],

                        refresh_token = refreshedTokens[refresh_token],

                        expires = refreshedTokens[expires]

                    ],

                    // Save the new tokens in the static variable

                    _ = (cachedTokens <- newCachedTokens),

                    // Use the new tokens

                    tokensToUse = newCachedTokens

                in

                    tokensToUse

            else

                // Use the existing cached tokens if they are still valid

                cachedTokens,

        // Extract the access token from the tokens to be used

        validAccessToken = tokens[access_token]

    in

        validAccessToken;

When the token expires, you can return an HTTP 401 error to prompt the user for reauthentication.

if isTokenExpired then

    error Error.Record("TokenExpired", "Access token has expired. Please reauthenticate.")

In case of reauthentication, yes, the user will need to go to the Data Source Settings in Power BI, clear the old credentials, and reconnect the connector with new credentials.

If this helps, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details.
Thank you.

[v-hashadapu]

Hi @ArvindBhatt , thank you for reaching out to the Microsoft Fabric Community Forum.

Yes as @rajendraongole1 said Power BI does not automatically refresh the access token once it expires. You will need to manually refresh the token before making API calls. Here's how you can do it:

1. When the user logs in, save the refresh token provided by the API.
2. Use the refresh token to request a new access token. This request should be sent to the API's token endpoint using the POST method.
3. Replace the old access token with the new one received from the API.

Example in M code:

let

    refreshToken = "your_refresh_token_here",

    clientId = "your_client_id_here",

    clientSecret = "your_client_secret_here",

    tokenEndpoint = "https://api.example.com/oauth2/token",

    body = "grant_type=refresh_token&refresh_token=" & refreshToken & "&client_id=" & clientId & "&client_secret=" & clientSecret,

    response = Web.Contents(tokenEndpoint, [Headers=[#"Content-Type"="application/x-www-form-urlencoded"], Content=Text.ToBinary(body)]),

    newAccessToken = response[access_token]

in

    newAccessToken

If this helps, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details.
Thank you.

[rajendraongole1]

Hi @ArvindBhatt  - Power BI does not automatically refresh access tokens for custom connectors. You must handle token refreshing manually within your custom connector.

Below is an example pattern for refreshing an access token:

RefreshToken = () =>
let
Response = Web.Contents("https://api.example.com/token", [
Content = Text.ToBinary("grant_type=refresh_token&refresh_token=" & RefreshToken),
Headers = [
#"Content-Type" = "application/x-www-form-urlencoded",
#"Authorization" = "Basic " & Base64EncodedClientCredentials
]
]),
ParsedResponse = Json.Document(Response),
NewAccessToken = ParsedResponse[access_token],
NewExpirationTime = DateTimeZone.UtcNow + #duration(0, 0, 0, ParsedResponse[expires_in])
in
[Token = NewAccessToken, Expiration = NewExpirationTime];

Handling authentication for Power Query connectors - Power Query | Microsoft Learn

Custom Connector with Token Refresh - Microsoft Fabric Community