Passing bearer token generated in one query to another query returning a Formula.Firewall error

[mkjit256-outloo]

Hello,

I was able to get an authorization token, which changes every hour, by providing a username and an api_key in the following query :

let
   username = "xxxxxxx",
   api_key = "xxxxxxxxxxxx",
   #"BaseURL" = "xxxxxxxxxx",
content =
       "{
           ""username"" : """& username &""",
           ""api_key"" : """& api_key &"""
       }"
    ,
Source = Json.Document(
       Web.Contents(
           #"BaseURL",
           [
               RelativePath = "/xxxxxx/v1/authorize",
               Headers=[
                       #"content-type" = "application/json",
                       #"cache-control" = "no-cache"
               ],
               Content=Text.ToBinary(content)
           ]
           )
   )
in
    Source[token]

Then i have a single column table:

ID

1xxxx-xxxxx-xxxxxx-xxxxx

2xxxx-xxxxx-xxxxxx-xxxxx

 to which i would like to add a new column, using an invoked custom function. The function has the ID column as input, and should be also provied by the token: the body of my function is : 

(id as text)=>
let
   #"BaseURL" = "xxxxxxxxxx",

Source = Json.Document(
       Web.Contents(
           #"BaseURL",
           [
               RelativePath = "/xxxxxx/v2/xxxxx?id=" &id,

               Headers=[
                       #"Authorization" = "bearer " &token
               ]

   )
in
    Source

but when applying this function on the table, i am getting the following error:

Formula.Firewall: Query 'function_name' references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

 - How is it possible to achieve my goal of passing the generated token in one query to another query? 
PS: I am generating the token in one query and then passing it to another query instead of combining the two query into one, as i am using the same token for several queries within my model. 

[mkjit256-outloo]

Thanks for your reply

The single column table, should not exceed the 100 rows (has two rows currently 😀 ), but wondering now how it is possible to batch together multiple rows in a single request?

[ams1]

Hi,

I'm guessing you want to solve this WITHOUT ignoring privacy level settings, right? 😀

[mkjit256-outloo]

Thanks for reaching out...

The report i am building now is to explore how to the API requests from Postman to Power Query, and it will not published to a shared workspace. That is why i have hardcoded the username and api_key for now.
The next step will be to create a custom connnector, and at that step Privacy levels settings will be considered.... 

Hope i did answer you question/comment.

[ams1 · Accepted solution]

The thing is that privacy levels are the cause for your Formula.Firewall error - and are a good way to warn you that you are combining data from different sources (maybe you don't trust them all) -> "ignoring privacy levels" (warning: are you sure? ARE YOU REALLY SURE?) 🙂 should clear the error and allow you to continue with the exploration.

I tested your snippets locally with minor changes and they work with "ignore privacy level" set.

P.S. Don't know how many rows you will have in that single column table, but each row will hit the api endpoint with a request... fast. If you have MANY rows and control over the api, after testing if you get not so good performance, one option could be to batch together multiple rows in a single request.