Howe to get API data using Bearer Access Token dynamically?

[Sachintha]

I have an API that uses Bearer Access Tokens to retrive data. As such, I've got two API links; first one to get the access token, and the second one to get the data using the said access token.

The bearer access token retrival syntax looks something like this.

let
    Source = Csv.Document(Web.Contents("https://my-api-url", [Headers=[Authorization="Bearer access_token"]]),[Delimiter=":", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None])
in
    Source

The access token goes after the word 'Bearer '.

Now, this works and I can retriew data just fine. But the issue is I want this to be automated, and make this a daily data pull. But since every time the access token itself is different, how to I make the access token a variable within the above syntax?

The access token itself is pretty simple and I get it in a table like this:

[gracyaz111121]

Hi, can you set automatic refresh for the datasets with api dynamic access token ? It is not possible for me since the token expires and api accepts only "Anonymous" as data source credentials setting

[Sachintha · Accepted solution]

I figured this out myself, and detailing here for someone else who might need it.

First, get the Access Token and convert it into a table:

let
    Source = Json.Document(Web.Contents("api-to-get-access-token")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Sorted Rows" = Table.Sort(#"Converted to Table",{{"Name", Order.Descending}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Sorted Rows", [PromoteAllScalars=true])
in
    #"Promoted Headers"

The sort rows and promote headers are just so I will have one row. The final result looks like this:

TABLE A:

Next, convert the original API call, which uses the access token value, into a function:

(token as text) =>
let
    Source = Csv.Document(Web.Contents("api-to-get-data", [Headers=[Authorization="Bearer " & token]]),[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
    #"Promoted Headers"

Then, on the above TABLE A, invoke the custom function created (go to 'Add Column' tab on the menu, select 'Invoke Custom Function'), and select the newly created function, and select the value of the 'Bearer' column as the value to pass to the 'token'.

This will give you a new column, which you can expand to get the data.

[ams1]

Hi @Sachintha 

For the first API that gives you the token, what do you use for authentication?

[Sachintha]

I simply use the URL, no specific authorization. Like below:

let
    Source = Json.Document(Web.Contents("my-api-url?client_secret=some_key&grant_type=client%5fcredentials&client_id=some_id")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Promoted Headers" = Table.PromoteHeaders(#"Converted to Table", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"token_type", type text}, {"Bearer", type text}})
in
    #"Changed Type"