Formula.Firewall.. please rebuild this data combination

[baouss]

Hello

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

Although this error is extensively written about in the forums and the web, I do not feel I can use the suggested workarounds, like for example here.

Bottom line, the workaround is about frist loading the external data, putting it into a staging query, and then use this later on.

But what if this is not possible?

In my case: There is a table with 900 rows, 2 columns

col1 = companyName

col2 = LEI (some kind of ID)

col3 custom column = GleifAPI: Make HTTP GET request with [LEI] as query parameter --> This is where it breaks

Basically a "vlookup" to an external data source.

In such a case Ken Puls (blogger from link above) suggests turning of the formula firewall as a last resort. Although I do know how to do this in Excel or Pwer BI Desktop, I cannot do this in SSDT because I want to deploy to Azure AS.

What can I do? Below you'll find the exact queries:

1) Here the external lookup encapsulated into a function:

let api =
(lei as text) =>
let
    url = "https://leilookup.gleif.org/api/v2/leirecords?lei=" & lei,
    Source = Json.Document(Web.Contents(url))
...
in
   Source
in api

2) The query using the function for data lookup for each row:

let
    Source = #"dimCounterparty",
...
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "GleifAPI", each GleifAPI([LEI]))
in
    #"Invoked Custom Function"

Cheers baouss

[v-chuncz-msft]

@baouss,

Try setting Power BI Desktop privacy levels and create a support ticket if necessary.

[baouss]

@v-chuncz-msft thanks for answering! However, as stated above I'm using SSDT and not Power BI Desktop. Below is an abstract of the model.bim which shows that I have fast combine enabled and not set any privacy levels. As far as I can tell there are no more privacy related settings I can manage within SSDT, are there?

When I try to replicate this within Excel it works 😞 SSDT seems to be lacking in that area? Regretfully, since it ought to cater to advanced use cases.