Formula.Firewall: Please rebuild this data combination

[MiffMaster]

So I have run into an issue that is causing some serious headache.

Some Power Query M

let
    // Constants
    _driver_ = "driver={PostgreSQL Unicode(x64)};",
    _database_ = "server=MY_SERVER_NAME;port=5432;database=MY_DATABASE",
    _table1_ = "MY_DATABASE.MY_SCHEMA_1.MY_TABLE_1",
    _table2_ = "MY_DATABASE.MY_SCHEMA_2.MY_TABLE_2",

    // Query table 1
    _Q1_ = "SELECT col_1, col_2, col_3 FROM " &_table1_,
    #"Table 1" = Odbc.Query(_driver_ & _database_, _Q1_),

    // Query table 2
    _Q2_ = "SELECT col_A, col_B, col_C FROM " &_table2_,
    #"Table 2" = Odbc.Query(_driver_ & _database_, _Q2_),

    // Filter table 2 using content in column from table 1
    COL_2 = Table.Column(#"Table 1", "col_2"),
    #"Filtered Table" = Table.SelectRows(#"Table 2", each List.Contains(COL_2, [col_A]))
in
    #"Filtered Table"

The ideal way (for me) would be to first load COL_2 then use that data to craft the SQL query Q2 such that I only request the data I need from table 2. However, that is an obvious data leak.

Above I attempt to avoid this leak by first loading all data from table 1 & 2, then filtering table 2. This should not send data from table 1 to the server when loading table 2. But the Formula.Firewall still complain.

I suspect that Power Query might be trying to be clever in the background. So I tried to stop that by preventing query folding like this.

    #"Table 1" = Table.StopFolding(Odbc.Query(_driver_ & _database_, _Q1_)),
    #"Table 2" = Table.StopFolding(Odbc.Query(_driver_ & _database_, _Q2_)),

I also tried to force loading of data before filtering like this.

    #"Table 1" = Table.Byffer(Odbc.Query(_driver_ & _database_, _Q1_)),
    #"Table 2" = Table.Buffer(Odbc.Query(_driver_ & _database_, _Q2_)),

But Formula.Firewall still insist that I rebuild my query as I "may not directly access a data source".

[BA_Pete]

Hi @MiffMaster ,

If you're only using 'queryTable1' as a dynamic filter, then I'd suggest the following:

--1--

Put this part of your original query into its own query and disable load. Let's call it 'filterQuery':

let
    // Constants
    _driver_ = "driver={PostgreSQL Unicode(x64)};",
    _database_ = "server=MY_SERVER_NAME;port=5432;database=MY_DATABASE",
    _table1_ = "MY_DATABASE.MY_SCHEMA_1.MY_TABLE_1",
    _table2_ = "MY_DATABASE.MY_SCHEMA_2.MY_TABLE_2",

    // Query table 1
    _Q1_ = "SELECT col_1, col_2, col_3 FROM " &_table1_,
    #"Table 1" = Odbc.Query(_driver_ & _database_, _Q1_),

--2--

Create your 'queryTable2' as normal, removing any references to 'queryTable1'.

--3--

Now, on queryTable2, add a step that looks like this:

Table.SelectRows(
    previousStepName,
    each List.Contains(List.Buffer(filterQuery[col_2]), [col_A])
)

Power Query should stream your filterQuery[col_2] values to the queryTable2 source in the native query WHERE clause, so folding is maintained.

Pete