How to avoid Power Query Formula.Firewall permissions errors?

Question

I am using Power Query in Microsoft Excel 365 version 2301. I have a table with 2 columns of identifiers that is pulled from a web source (REST API). It looks something like this:

#"Identifiers"

let
    Source = #table(
        {"id_a", "id_b"},
        {
            {1234, 9813},
            {1096, 3018},
            {2841, 3852}
        }
    )
in
    Source

I have a function that turns those identifiers in to a web query string create_query_string = (id_a as number, id_b as number) as text => ...

and a function that uses that query string to perform a web query and return a list of results my_web_query = (url as text, query_string as text, api_key as text) as list => ...

I use those to add a new column with the results of that query

#"Query1"

let
    Source = Table.Buffer(#"Identifiers"),
    #"Added Custom" = Table.AddColumn(
        Source,
        "web query",
        each create_query_string([id_a], [id_b])
    ),
    #"Added Custom1" = Table.AddColumn(
        #"Added Custom",
        "results", 
        each my_web_query(
            "https://my.website.com/rest_api",
            [web query],
            "my_api_key"
        )
    )
in
    #"Added Custom1"

This executes correctly, but when I select one of the [List] results in a row, I get this formula firewall error:

Formula.Firewall: Query 'Query1' (step 'Added Custom1') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

All of my data sources that I can see are set to the same privacy level: "organizational" What do I need to do to be able to view the results of my query?

Answer 1

Microsoft recommended 2 solutions. https://learn.microsoft.com/en-us/power-query/data-privacy-firewall

1. "flatten" your queries such that each query pulls all the info it needs from each database
2. turn off the data privacy firewall.

I don't like either option, but #1 would cause me to re-pull the same data for each of my queries.

I went with Option 2 because (in my particular case) all the data sources are from within my org.