cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
markive
Advocate II
Advocate II

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

I am getting the following error:

 

 

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

 

In my first query I am accessing an API to get a list of URLs. In the second query it uses this list of URLs to then go back to the API and request all of the data.

 

I understand what the complaint is, but how do I get around it? I am actually always hitting the same domain for both queries, but maybe it doesn't know that. I suppose it wouldn't know that until runtime.

 

I have read that in Excel PowerQuery you can go to:

 

File=>Options and Settings=>Options and set the Ignore Privacy Levels option.

 

But no such option seems to exist in Powe BI Desktop. Does anyone know what I'm doing wrong or how I can get around this restriction, it seems like a common usage scenario?

 

 

 

1 ACCEPTED SOLUTION

 

Ken Puls blogged about this here

http://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

32 REPLIES 32

After a couple hours I found a workaround for the problem I was having but it really just confirms what I already think about M.

 

I found that the problem I was having was NOT down to accesing an external data source (all of my data loads are done in functions) but because I load a table, create two filtered tables from that table and then join them together. Why it would object to that I don't know, but it does ... I suspect (as per my previous post) that it's something to do with the lazy interpretation and it getting confused!

 

I then moved the splitting and re-joining of the table into a function script and it worked!

 

I think M / Power Query is a fantastic tool but it needs so many improvements ...

1. Ability to save files from within the Power Query environment ... qiute a few times Excel has hung/crashed when exiting the environment and I have lost hours of work!

2. Ability to export all scripts to text files in a specifed folder.

 

Would be nice ...

1. The Advanced Editor name is a bit of a joke as it anything but!. Colour-coding and all of that would be nice BUT for starters how about some basic Notepad-style editing features such as Search and Replace?

2. I really wish I could change the editor font size as it is too small, I don't want to have to change all of Window settings just because of this.

3. Environment state. It would be nice if the opened/closed groups stayed opened/closed in Power Query and the Excel sidebar, I constantly have to close the groups I'm not interested in.

 

Anonymous
Not applicable

100% agree with both these statements:

 

1. Ability to save files from within the Power Query environment ... qiute a few times Excel has hung/crashed when exiting the environment and I have lost hours of work!

2. Ability to export all scripts to text files in a specifed folder.

 

In essence, the option to do source control in some practical way from Excel PowerQuery and PowerBI PowerQuery.

"I think M / Power Query is a fantastic tool but it needs so many improvements ..."

 

As far as I m concerned, I would say only :

 

"it needs so many improvements ..."

So, regarding editing of Power Query, I just found out that there's an addon for Power Query in Visual Studio (via Adam Saxton), and just started using it. He also mentioned that there's one for Visual Studio Code as well. 

 

Ken Puls blogged about this here

http://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

The error can occur from privacy permissions as well. It may be necessary to take the steps outlined in the solution above and also to change the privacy options.  File>Options and settings>Query Options>Privacy

Then choose "Always ignore privacy level settings".

I actually ran across this same error...

Going to: Options --> CURRENT FILE --> Privacy then setting the Privacy Level to "ignore the privacy levels and potentailly improve performance" did the trick.

 

Thank you so much guys!

@zuludogm 

Fernando

I'm getting same error but Im not using excel sheet but sql stored procedure query. Is there any solution for this?

@MattAllington Thank you for sharing, the Blog by Ken Puls, saved alot of my hair. (Very frustrating, but the below made sence and helped alot).

SNAGHTML409dfd44

Hi @MattAllington, thanks for the info.. 

 

Yes I was able to get around it just now by effectively moving all of the steps from the other query into the same query. This gets around the problem but it's a bit annoying.

 

@markive 

All steps in the same Query! Without "Reference"!!!

This did the trick!

You are a lifesaver!!!

Thank you so much!

Michael

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.