cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KimStahl94
Helper I
Helper I

Formula.Firewall Error which disappears after reloading the data model in PBI Desktop

Hi there,

I was wondering if anyone could help me after I did not find any solution that helped me removing the following error:

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

I already read the following topics on this topic but none had the solution. Except, I failed to execute the instructions:

1. https://docs.microsoft.com/en-us/power-query/dataprivacyfirewall (Every merging step is executed in a seperate query)

2. https://excelguru.ca/power-query-errors-please-rebuild-this-data-combination/ (All data is loaded into a staging area before merging steps take place, disabling privacy levels are still activated, deactivating did not help)

3.  https://www.thebiccountant.com/2020/11/16/improve-file-import-from-sharepoint-in-power-bi-and-power-... (I rebuild my SharePoint query to enhance the performance)

I also changed the data type from excel to csv. I am retrieving data solely from two SharePoint 365 folders with several subfolders (privacy level: organisation). I reshaped my query a dozen times and am still recieving the Error after loading my model in PBI Desktop for >15 minutes. After reloading within Power BI Desktop without any changes made the data model will be loaded.

 

If anyone of you guys has an idea what I am missing, please help me out. I can provide you more information if needed.

Regards

KS

6 REPLIES 6
lbendlin
Super User
Super User

(Every merging step is executed in a seperate query)

Per my current understanding (and I may be wrong) that is the issue.  To avoid the formula firewall you should keep everything in the same partition.

Hi @lbendlin,

thanks for your answer. In my understanding a partition is created after a merging step is executed. That is why I changed my query from the single query (left)  to the three queries (right).

KimStahl94_0-1659794018315.png

 

How could I keep it all in the same partition if a new partiton is created after the merge? Or am I getting the term partitions wrong? 
Important note: Privacy Levels are all set to organisational. Shouldn't the firewall not be a problem then?

Regards
KS

Avoid merging at all cost.  Either do the joins further upstream, or use separate dataflows, or let the data model do the work for you.

Hi @lbendlin ,

are there other transformations that I should avoid too? Like Invoking custom functions? It seems as if the error also appears for queries that do not have any merging steps at all. Therefore I use the expand function in the PQ UI that generates and invokes a automatically generated function.

Regards
KS

Avoid anything that makes the Power Query engine think it is dealing with separate data sources.  If you browse the interwebs you will find all kinds of trickery to create a smoke curtain so that the engine gets confused, and assumes everything is ok.

 

Small example: declare functions inside your query, not outside.

Hi @lbendlin 
I'm trying this out at will let you know if it works. Unfortunately I have not been working with dataflows yet. All merging steps are executed in the end of my query. I will let you know how things turned out.
But thanks so far, I really appreciate it!
Regards
KS

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.