Need help understanding “Formula.Firewall: Query references other queries error

[MAVIE]

Hi, 

I have the following query, which is used to create my Users dimension. 

let
    #"Join Active Users" = Table.Join(StgDimUsers, {"Id"}, StgActiveUsers, {"UserID"}, JoinKind.Inner),
    #"Removed Columns" = Table.RemoveColumns(#"Join Active Users",{"UserID"}),
    #"Extract Domain" = Table.AddColumn(#"Removed Columns", "Company", each Text.BetweenDelimiters([Email], "@", ".") ),
    #"Changed Type" = Table.TransformColumnTypes(#"Extract Domain",{{"Company", type text}})
in
    #"Changed Type"

It joins two staging tables. One for all users, and one of active uses.

This join is resulting in the error: “Formula.Firewall: Query 'QueryName' (step 'StepName') references other queries or steps and so may not directly access a data source. Please rebuild this data combination.” Though I do not quite understand why. 

Both staging tables are using web queries to the same source, and have been set to privacy level "Organizational".

From reading documentation about the error: 
Power Query Errors: Please Rebuild This Data Combination (excelguru.ca)
Behind the scenes of the Data Privacy Firewall - Power Query | Microsoft Learn
it would seem that splitting your data queries into staging tables should resolve the issue, but this does not seem to work for me, or perhaps I do not understand the error correctly. 

Can anyone help me understand where the issue lies?

[MAVIE]

Hi @rubayatyasmin, 

Thank you for the quick response

I have looked at all of the possible solutions and the only one which seemingly works is "Fast Combine" i.e. to ignore privacy level settings. This is pretty unsatisfactory given that the report can deal with sensitive data. 

It does appear that some data sources are not present in the data source settings as I am using the same source but different end points as relative paths in my web queries. I can therefore not changes the privacy level settings for each idependently but as a group, and it seems that this does not work. 

[rubayatyasmin]

I understand, fast combine is not a good solution. can you try table buffering? You can use the function Table.Buffer() to force Power Query to load the entire table into memory, which can sometimes prevent this issue. Be aware that this can increase memory usage, especially with large tables.

[rubayatyasmin · Accepted solution]

Hi, @MAVIE 

The error you're encountering, the "Formula.Firewall" error, is a common error in Power Query and it's usually related to Power Query's privacy settings. Power Query is designed to prevent information from being inadvertently shared across data sources. The settings that control this behavior are known as privacy levels, and they are applied to each data source you connect to in Power Query.

When two queries are combined and Power Query can't determine the privacy level of the sources, it blocks the execution of the combined query and gives a "Formula.Firewall" error.

Even though you've set your privacy level to "Organizational", there could still be other issues causing this error. Here are some possible solutions to this problem:

1. Ensure Privacy Levels are Correctly Set: Although you mentioned you've set your privacy levels to "Organizational", you might want to double-check this setting. Sometimes, privacy settings may not have been applied correctly, especially if you have multiple data sources.

2. Use Power Query's 'Merge Queries' Option: If you're not already, consider using Power Query's 'Merge Queries' option rather than the Table.Join function. This could sometimes help in avoiding the firewall error.

3. Enable 'Fast Combine': Enabling 'Fast Combine' could potentially resolve this issue. 'Fast Combine' essentially tells Power Query to ignore privacy levels. You can enable 'Fast Combine' in Power Query Options under the Global Privacy section. However, be cautious with this setting as it might not be advisable if you're dealing with sensitive data.

4. Use Staging Queries: Staging queries can help when dealing with complex transformations. Each staging query should load data from only one source. Then, create a final query that merges the results of the staging queries.