Dynamic Authorization Token from Rest API leads to Formula.Firewall-Error

[PhilippeVoyame]

Hello Everybody

Im trying to connect trough a REST Api to an external Service.

The challange ist, that I have to aquire an Access Token which is only valid for 24 hours.

I managed to load the respective Token with the following power query:

let
   body = "{""username"":""xxxxxx@xxxx.com"",""apiKey"":""***********""}",
   Data= Web.Contents("https://xxxxx.com/api/v3/tokens",[Content=Text.ToBinary(body),Headers=[#"Content-Type"="application/json"]]),
   DataRecord = Json.Document(Data),
   Source=DataRecord,
    access = Source[access],
    #"In Tabelle konvertiert" = Record.ToTable(access),
    #"Erweiterte Value" = Table.ExpandRecordColumn(#"In Tabelle konvertiert", "Value", {"expires", "timezone", "id", "tenant"}, {"Value.expires", "Value.timezone", "Value.id", "Value.tenant"}),
    #"Value id" = #"Erweiterte Value"{0}[Value.id]
    
in
    #"Value id"

I converted the table, as the Query returns not only the AccessToken but a String of information, including the validity date and more information. 

The outcome results in the desired String with the AccessToken:

I then wanted to use the Token in the PowerQuery of another Table:

let
    Quelle = Json.Document(Web.Contents("https://xxxxxxxxx.com/api/v3/clients", [Headers=[Token=GetAccessToken]]))
in
    Quelle

This leads unfortunatly to the Formula.Firewall-Error

I read related articels and questions, but did not manage to translate their specific Problems to my issue.

So I do appreaciate you Input on how I might proceed.

Best Regards Philippe

[mahoneypat · Accepted solution]

In that case, you could set up a Flow to make the token request daily and write to a file in the cloud (or SP list), and then use that file as a source in your query to harvest the token prior to refresh.  You can make http calls from flow too.  If needed, you could also send your token query results over to flow to trigger it as a JSON packet (but that is trickier).

Regards,

Pat

[PhilippeVoyame]

Cool Idea, haven't thought of that!

Will try and I'm confident that will solve my issue.

[mahoneypat]

Please see this post in which I attach a pbix that shows an example of getting a token and then using it in other queries on Twitter.  Hopefully it will help you get unstuck.

Re: Twitter API to Power BI - Microsoft Power BI Community

Regards,

Pat

[PhilippeVoyame]

Hello Pat

Thanks a lot for your response. Managed to open the File (must have had a conflict with my PowerBI version on my Business Computer). 

I do understand that you integrate the Token Request in the Same Query Statement you use to receive the Data. 
As I will have to obtain about 10 Tables trough the same API to fully build my reporting model, I was looking for a method to store the Token centrally and reuse that respectively. 

Do I understand your answer correctly that this is not possible in PowerBi?

Best Regards

Philippe