Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nixonyx
Helper I
Helper I

Formula.Firewall - Query references other queries...

I see that this error crops up quite regularly on these fora but I haven't found a scenario that matches my experience.

 

A former team member (who has left the organisation) created quite a complex set of reports that reference SQL tables, with data manipulation using lots of R scripts.

 

Another team member has a stand alone PC that can refresh the report. It's one that's separate from the corporate network with local admin rights.This team member is moving on and I need to take over maintenance of this report and try to make improvements.

 

However, when I try to refresh this report, I get the Formula.Firewall error on a query that starts off with a huge chunk of SQL and then switches to a number of R scripts to transform the data ready for presentation in the report. At this point, I feel I need to say - I know nothing about R! It looks like witchcraft 😄

 

My assigned machine does not afford me local admin rights so when I installed the required R packages the script calls they are put into a personal library. However, I can use a personal PC with full admin rights that puts the packages in the default location. I get the same behaviour on both devices.

 

The reason I'm focussing on the R element is because, in my quest to troubleshoot, I have disabled all queries that have an R component. They all refresh just fine. I tried to break down the query I mention above into 'baby steps' and it falls over as soon as it hits the R script.

 

I have removed the latest version of R and installed the version that is on the PC that can refresh this report. Still get the Formula.Firewall issue. Does the version of Power BI matter? That's the one thing I'm having trouble (a) determining on the working machine, and (b) working out how to roll back my two machines to an earlier version.

 

I've looked at the R script settings and notice that the installation path is correctly detected but the IDE defaults to Other with nothing in the file path field. There is no option to select another R IDE and adding a path to the launch exe doesn't fix the issue.

 

This report cannot rely on one single machine to refresh it so I need to know what other troubleshooting I should try in order to get this resolved?

 

All machines are running Win10 professional.

 

I hope this makes sense! I'd be very grateful for any/all suggestions.

7 REPLIES 7
v-shex-msft
Community Support
Community Support

Hi @nixonyx ,

According to your description, it sounds like a common power query error message when you invoke other query steps. Maybe you can take a look at following link to know how to deal with this:

Power Query Errors: Please Rebuild This Data Combination

Please understand that this link is provided with no warranties or guarantees of content changes, and confers no rights.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thanks for the suggestion.

I did follow that process, it's what I meant by breaking the query down into 'baby steps'. I separated the bigger query into multiple smaller, staging queries and still get the Formula.Firewall error when running the R script that uses those queries as the source

I will try again, maybe I put the break point at the wrong stages

Hi @nixonyx ,

You can click on query table steps(applied steps panel) to review the result of that step. Please find out the first query steps who appears the error message.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

The query fails at the R script step.

I have broken the query into as many individual steps as I can and still it fails with Formula.Firewall error at the R script step.

It runs fine on one PC but no other device. Rolling back the version of R to match the working device version hasn't worked. I am not sure how to determine what version of PBI desktop is running and whether it is from the Win10 store or downloaded exe.

Hi @nixonyx ,

I guess your query table not contain r script code, right?

If this is a case, I think it should invoke r script code from a local file, you can find out local file and move it to new device to fix this issue.

Running Local R Scripts in Power BI

Please understand that this link is provided with no warranties or guarantees of content changes, and confers no rights.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thank you for the suggestion. I have had a look at the scripts and I cannot see any external links. The R scripts appear to be within the query. I'm assuming, from what you suggest, that I should see a file path reference to an external script? This is not evident in the queries.

However, you have given me an idea! Maybe I should move the scripts out of the queries and onto a network share? Would that work?

Have you tried to separate your R script from directly referencing your data source script by using a function? You can create a staging query for your data source and then create a function on it to output a new query, and then you use the new query to do all the R transformations. Here is a post about that: Formula.Firewall Error Referencing Queries – R & Python Scripts

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.