Power BI
Needs VotesChange the Power Query Formula engine to adhere to sub-queries for none loading entities
Pär Adeen on 02 Oct 2020 11:52:45
Referring to this excellent article by Ehren: https://social.technet.microsoft.com/Forums/en-US/ca434e2d-88fe-4962-b46a-a1db51e8bd89/feedback-wanted-behind-the-scenes-of-the-data-privacy-firewall, as well as my (erapade) comment, there's probably improvements that could be done in the way the Formula engine partitions. I.E. sub-queries (referenced none loading queries) should never be a partition for itself. It this is feasible, we can get rid of both some irritating Formula FW errors, as well as speeding up many other queries.
- Comments (2)
RE: Change the Power Query Formula engine to adhere to sub-queries for none loading entities
Agree this is something that needs updating/fixing. I'll give an example. I'm creating a sources table that I want to use as the reference point to manage all sources and data loads (there's other ways of doing what I'm doing - but this is just an example regardless). It will have columns like "Source Path", which I then want to look up to load an Excel sheet in another query. For obvious reasons, I get the above mentioned error:
Formula.Firewall: Query 'OpenReferencedExcel' (step 'Open Referenced Excel Func') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
What I would be looking for in a working implementation is one of the following solutions:
- In the "Data Source Settings", all sources of the same privacy level (I.e. Organisational) should be able to interact in the same query
- In the "Data Source Settings", a way to whitelist sources against each other
- Or, an mquery whitelist command, or guard lowering that allows for me as the creator of the mquery to acknowledge that I'm breaking the firewall rules I.e.
Source = #"Other List"{0}[Source Path],
#"Open Data Source" = Formula.WhiteListFirewall(Excel.Workbook(File.Contents(#"Source"), null, true))
The intention being that within the Formula.WhiteListFirewall (or whatever context and function name combination you want to call it), the executing expression is allowed and returned.
As I see it, the options above allow for control in increasingly more granular ways, and conversely allow for leaks to be controlled in increasingly more stricter ways. The first one is likely not acceptable as a blanket solution - but surely between the other two (or some other unlisted option) there's a way of manually indicating that we don't consider the data leak to be a threat.
RE: Change the Power Query Formula engine to adhere to sub-queries for none loading entities
The title is wrong and I can't change. It shall be "Power Query Formula Firewall" and not "Power Query Formula Engine". the same in the text body