Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
(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).
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |