Quick fix for Formula.Firewall issues in Power Query and Power BI

Formula.Firewall issues can hit you when designing your queries or even “out of the blue” when suddenly refreshes in the service are failing due to changes in the query evaluation.
You will find a lot of methods published on the internet which are good and cover different scenarios. But there is also a very quick fix method that I learned from Miguel Escobar that I want to demonstrate in this post. This will basically circumvent the data privacy level, so make sure that you understand the implications (risk of data leakage from one source to another). If not, please read Miguels article first!

Privacy Levels workaround for Formula.Firewall issues

In my sample, I have a query where I want to append another table (“ContestTypes”) to the result of the previous step (“Expanded Custom”):

Formula.Firewall Error in Power Query

To resolve this issue, I go to the query that contains the table to append (“ContestTypes”) and convert it to a function. And to keep that query easy to debug, I add the step to convert that table-query into a function-query at the very end like so:

Convert query into a function

Prefixing the reference to the previous step with ()=> will turn it to a function without any input parameters.

Then, back in my Berries-query this conversion will cause an error message, saying that the operator & cannot be used to combine tables and functions. But this can quickly be fixed by adding brackets to the (now) function reference: #”Expanded Custom” & ContentTypes()
This will call the function and return its result as the original table.

Now, if I had used the ContestTypes query in other queries or even had loaded it as a table to the data model, then all these actions would report errors after I converted the query into a function. To solve that, I go back to the ContestTypes query and add a step there at the end where I call the function so that it returns a table again: #”Convert to function”()
Then, with this last step selected, I select “Extract Previous” and choose “fnContestTypes” for this new “staging query”. I then reference this function in my “Berries” table instead. This will keep all existing references in place and I can use the “new” function in my query that previously had formula firewall issues.

Limitations

I have used this fix very often and works in many cases. But there are occasions where even this method wouldn’t solve the firewall issue. So just keep in mind that this might not be the magic bullet for all scenarios.

File to check the samples: Power-Query-Formula-Firewall-Workaround.pbix

Enjoy and stay queryious 😉