Hi,
I'm hoping someone can help me... I'm trying to connect Power BI to the Xero API to pull some details.
The API has two tokens:
- Access Token - valid for 30 minutes;
- Refresh Token - valid for a single use.
I've created a parameter for the Refresh Token, and I can use it in a Power Query to successfully connect to the API, get an Access Token and get the data that I'm interested in. However, in performing this query the Refresh Token is replaced by a new Refresh Token which needs to be stored somewhere to be used next time I hit refresh.
So, what are the options for saving my updated Refresh Token, and having it available to use next time? Can Power Query take the value and pass it back to update the Parameter?
I've pasted the query below:
let
// Concatenates the Key & Secret and converts to base64
authKey = "Basic " & Binary.ToText(Text.ToBinary(#"API Key" & ":" & #"API Secret"),0),
url = #"Token URL",
// Uses the Xero POST oauth2/token method to obtain a bearer access token and new refresh token
GetJson = Web.Contents(url,
[
Headers = [#"Authorization"=authKey,
#"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
Content = Text.ToBinary("grant_type=refresh_token&refresh_token=" & #"Refresh Token")
]
),
FormatAsJson = Json.Document(GetJson),
// Gets token from the Json response
#"RefreshToken" = FormatAsJson[refresh_token], //This is the updated Refresh Token that I need to save somewhere
AccessToken = FormatAsJson[access_token],
AccessTokenHeader = "bearer " & AccessToken,
// Uses the Xero Project GET method using the bearer token from the previous POST oauth2/token method
GetJsonQuery = Web.Contents(#"Projects URL",
[
Headers = [#"Authorization"=AccessTokenHeader,
#"xero-tenant-id"="xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"]
]
),
FormatAsJsonQuery = Json.Document(GetJsonQuery),
items = FormatAsJsonQuery[items],
#"Converted to Table" = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"projectId", "contactId", "name"})
in
#"Expanded Column1"
Any assistance on how to grab the updated refresh token, and push it back to the parameter, or alternatively any other option to store my refresh token somewhere else (not a parameter) and use it in the query and have the query update it, would be greatly appreciated.
Cheers,
Andy
