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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sachintha
Advocate I
Advocate I

Howe to get API data using Bearer Access Token dynamically?

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:

at.png

 

1 ACCEPTED SOLUTION
Sachintha
Advocate I
Advocate I

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:

AccTok.png

 

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'.

CallData.png

 

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

 

 

View solution in original post

3 REPLIES 3
Sachintha
Advocate I
Advocate I

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:

AccTok.png

 

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'.

CallData.png

 

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

 

 

ams1
Super User
Super User

Hi @Sachintha 

 

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

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"

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors