Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to 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.
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.
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.