Forum Discussion

MiffMaster's avatar
MiffMaster
Regular Visitor
3 years ago

Formula.Firewall: Please rebuild this data combination

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".

Need Help

1 Reply

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    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