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
PhilippeVoyame
Frequent Visitor

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

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:

PhilippeVoyame_0-1607683329097.png

 

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

 

PhilippeVoyame_1-1607683577382.png

 

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

1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

4 REPLIES 4
mahoneypat
Employee
Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Cool Idea, haven't thought of that!

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

mahoneypat
Employee
Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

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