Formula.Firewall error when using if-then-else

[Anonymous]

Hello,

unfortunately I couldn't find anything related to this anywhere so I hope that someone has had a similar case and can provide a solution to this problem.

The situation is basically like this: Pretty much everytime I am using if-then-else my Query results in a Formula.Firewall error.

Let me give you a specific case:

I am using Power Query to get data from an external source.

This data is in JSON format.

After pulling the data there are some transformations (to table, remove columns, reorder columns, etc.) nothing special here.

But in some cases my table does not come from one single call to an external source but rather a combined table from multiple sources, depending on a parameter.

For example:

If my parameter "STATES" is "NO" I am pulling data from https://somedatabase/country=ES&city=Madrid

This data will then be transformed into a table.

Lets call this step: Data_No_States

After that futher transformations are done.

If my parameter "STATES" is "YES" I am pulling data from https://somedatabase/country=US&city=New_York and https://somedatabase/country=US&city=Buffalo and https://somedatabase/country=US&city=Kingston

After that I combine them to one table.

Lets call this step (after combining): Data_Yes_States = Data_Yes_States_Combined

After that further transformations are done, just like in the above case.

So basically I am trying to do steps based on conditions.

Some conditions are given by parameters that the user can enter and some on conditions based on the data itself.

My resulting table is therefore dependent on a parameter.

It will look something like this:

Data_States = if STATES then Data_Yes_States_Combined else Data_No_States

This part is what causes the Formula.Firewall error.

I tried to simplify the example but in my actual Query there are more and more complex structures like that.

The problem is still the same.

Using if-then-else statements cause this error a lot of times.

I managed to use it without getting the Formula.Firewall error but I can't seem to figure the reason for why it sometimes works and sometimes causes the error.

I have read the book Collect, Transform and Combine Data using Power BI and Power Query in Excel by Gil Raviv and it helped me a lot before, but this time I can't find a solution to this problem. Looking at the Query Dependencies I can see that my Query is not dependent on other Queries besides my Current Workbook.

It would be nice if I could try to avoid ignoring privacy levels.

Thank you,

LPLA

Edit: I think I have figured out a solution but I'd still like to hear your ideas. Maybe there is a more elegant solution to this than the one I am currently trying.

Edit2: It seems like my solution only shifts the error to different stages but does not provide a final solution.

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

Ken Puls blogged about this here

Power Query Errors: Please Rebuild This Data Combination

And, @freder1ck summed it upbrilliantly in the following post: "...each query needs to be loaded into a staging query first. Then, you can combine these staging queries." 

Formula.Firewall: Query references other queries, so it may not directly access a data source.

Try to refer to this post in the hope of helping you.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

The formula firewall is there to prevent accidental or malicious cross-site scripting.  That's what your code is trying to do, basically.

One (crude) remedy would be to run both queries first (you will need to set the privacy level accordingly) and then run the if then else logic.