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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

54 REPLIES 54
Gebrel
New Member

For people using multiple APIs and publishing to the Power BI Service in 2023, here is the solution that worked for me:

Power Query does not want you to reference another Query AND gather external data at the same time. So, if you need to use the result from one API to make more requests to another API you will have to do it all in the same Query.

 

Many suggest to just use the "always ignore privacy level settings" in the file settings, but it seems to me that it only works on your personal computer. When that same dashboard is published in the Power BI Service, that option will be ignored, and the dataset will not be able to refresh.

 

Here is the link from Power Query's documentation exlpaining how it works:

learn.microsoft.com/en-us/power-query/data-privacy-firewall#example-passing-data-from-one-data-source-to-another

 

Hope it helps 🙂

Any ideas on what to do if you need to use a function to iterate through paging? 

Hello, from my experience, you can indeed use functions to make iterating requests (even in the power BI service). I would advise to avoid making your request functions too complicated though, like accessing multiple sources at the same time or passing data between queries.

 

That is the best I can say without knowing the details of what youre trying to do right now.

Paw
New Member

For Excel folks out there (and for future myself) finding this thread on google:

Data tab -> expand Get Data -> Query Options -> Privacy -> Always ignore Privacy Level settings -> OK

janiersteven
Regular Visitor

Excelente respuesta, a mi me funcionó dando clic en esa ruta: File=>Options and Settings=>Options and set the Ignore Privacy Levels option.

sarsay
Regular Visitor

Spen
Regular Visitor

Thank you...

This sorted it for me.

Method 1: Ignore Privacy Levels

DaniMak1608
Advocate II
Advocate II

Six years later and the Privacy Option is still the (only) necessary fix to solve this issue. 😏

Is it going to cause any problem if the privacy level setting is turned off?? 

Yes it is a big superproblem, as it will not refres in webservices if you disabling privacy level

The privacy settings are designed to prevent accidentally sending sensitive data somewhere you shouldn't. Eg.  Imagine you have a table in SQL with staff details including salaries. You write a query that joins this data to Facebook data (for some reason). The privacy settings will stop the private data in SQL being sent to the open data source, Facebook. As long as you don't have any such queries, turning off the privacy settings will have no impact. Eg, as long as all your data sources are internal to your organisation. 



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

@MattAllington 

Thanks Matt!! That is very clear explanation!! 

Hennadii
Helper IV
Helper IV

Hi @markive , I had the same issue as you in 2016. Fortunately in 2020 there is Privacy Levels option which can be changed. If set it to "Ignore the Privacy Levels and potentially improve performance" the issue becomes resolved.

andrewbrick
Advocate III
Advocate III

For anyone still experiencing this issue, under File > Options, select Privacy, and if it's appropriate, select Ignore privay levels.  Even after following all the other suggestions referenced in this thread, I still got an error when refreshing.  It worked when I was in the Query Editor just fine (no errors), but refreshing from the regular report view would fail.

thank you

Anonymous
Not applicable

I can not believe this do work, and no failure rasied after clicking apply change. thanks, buddy!

Anonymous
Not applicable

The advice provided in this blog post now appears to be running contrary to experience in Power BI.

 

A query that contains all the Web.Contents calls a query needs leads it to function correctly. The splitting of the different Web.Contents calls into seperate queries before being merged into one final query leads an error to be thrown.

This ^^ 

you need to bundle all the bits together inside one query, rather than referencing values/tables generated by other queries. BI can't handle you bringing in other queries

 

For me it was do the following via 2 functions inside the same query (each time you use the API): 

  1. generate token function
  2. invoke the paged call, using said token

Recycles some code but it works

Anonymous
Not applicable

In case it helps others I ran into this issue after adding a non-supported/unofficial custom connector into PBI desktop and then trying to run queries against SharePoint files. 

Removing the custom connector from the local Documents > Power BI Destop > Custom Connectors folder got rid of the issue for me. (Note I didn't have any quries using the custom connector, just the fact it was there seemed to trigger the firewall thing).

david7f
Frequent Visitor

Thanks for the solution, I was struggling to solve this and now it works

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.