none
Formula.Firewall error specific situation RRS feed

  • Question

  • Hello, Chris!

    maybe, you can tell me what's the problem with the following code, that produces Formula.Firewall error?? The function that gets a table if called for 2 different tables causes the error. If we use a function with another name, it works.
    Thank you.

    let  
        xl = Excel.CurrentWorkbook(),
        
        get_table = (src) => xl{[ Name = src ]}[Content],
     //  get_table2 = (src) => xl{[ Name = src ]}[Content],

        head = get_table( get_hdata_HTable ),
        data = get_table( get_hdata_DTable ), // problem at final step data2 !
    //  data = get_table2( get_hdata_DTable ), works!

        cols = Table.ColumnNames( data ),

        head2 = Table.RenameColumns( head, List.Zip({ Table.ColumnNames(head), cols }) ),

        head3 = Table.RemoveColumns( head2,  cols{0} ), 

        cols2 = Table.ColumnNames( head3 ),

        data2 = Table.RemoveColumns( data, cols2 )
        
    in
        data2
    Saturday, November 30, 2019 3:17 PM

Answers

  • So I was able to reproduce the error using the M script you shared above. The error is being caused by the way that the firewall is partitioning your query. (Note that the firewall is optimized for the kinds of queries generated by the UI...i.e. queries where each step references the previous one, which is not what you're doing here.)

    Here's how the firewall partitions your query (I've moved some things around for clarity):

    Firewall partitioning diagram

    The "data2" partition is referencing the "cols" and "data" partitions. But it is also invoking get_table (in the head step), which results in a violation of the firewall's rule about "a partition can access data sources or reference other partitions, but not both". When you refactor your query to add some additional steps, this ends up changing how the query is partitioned, which allows it to work.

    My guess is that if you broke this up into multiple queries, it would work just fine.

    Ehren

    Wednesday, December 11, 2019 9:43 PM
    Owner

All replies

  • Hey

    Check the data source setting. Meaning you ignore privacy settings at all, or limit the data request to one call like this

        gett = get_table( "Tabelle1" ),

        cols = Table.ColumnNames( gett ),

        head2 = Table.RenameColumns( gett , List.Zip({ Table.ColumnNames(gett ), cols }) ),

       etc

    Jimmy


    Query it



    • Proposed as answer by jimmy80211 Sunday, December 1, 2019 7:31 AM
    • Edited by jimmy80211 Sunday, December 1, 2019 9:35 AM
    • Unproposed as answer by jimmy80211 Sunday, December 1, 2019 10:00 AM
    Saturday, November 30, 2019 3:24 PM
  • But, without wrapper function get_table or using another function it works correctly. The problem is when the same local function is used to get table by name
    Saturday, November 30, 2019 10:05 PM
  • Hello

    the only difference here is that in the erroneous code the data source is queried twice with the same function (and in this terms a parallel data flow with the same data). And this seems also to trigger the privacy function. This fact was never stated in an official documenation nor in other forums. Very interesting in fact.

    because once use one variable to get the data source, and use this in parallel (see my previous post) or use 2 different functions this works very well


    Maybe other more experience M-User can explain this

    BR

    Jimmy


    Query it

    Sunday, December 1, 2019 10:00 AM
  • Thanx for reply, but still cannot see the logic. Without wrapper function I can in one query get as much tables as I want and combine them to one table and after that do any combined table structure transformations. The fact that produces an error IN THE LAST STEP is that these tables are gotten via ONE wrapper function. If it would be the result of using multiple data sources in one query, I would get the sam error even without wrapper function. 
    Sunday, December 1, 2019 5:13 PM
  • And also, if I manually type column name to remove in the head3 step, it works. But it should not work if your explanation would be the description of the problem. head3 = Table.RemoveColumns( head2,  "xxx" ) works
    Sunday, December 1, 2019 5:19 PM
  • hey Bagration.555

    you'are right, really difficult to find a logic here. Starts all by assigning one data sources with the same wrapper function to TWO variables. So the easiest way the get around this is to assign it only to one variable. (it's also the better way to write M-code).

    For sure it would be nice to understand the logic behind this. Maybe somone that understands the M-engine is able to find the reason here.

    Jimmy


    Query it

    Monday, December 2, 2019 6:46 AM
  • Hi, Jimmy!

    Found another sight on the problem:

    let 
        xl = Excel.CurrentWorkbook(),    
        get_table = (src) => xl{[ Name = src ]}[Content],
     
        head = get_table( "x_1" ),
        data = get_table( "x_2" ),    
        cols = Table.ColumnNames( data ),
     
      //  this works, when adding extra variable (state) cols_h
         cols_h = Table.ColumnNames( head ),
         head2 = Table.RenameColumns( head, List.Zip({ cols_h, cols }) ),
     
      //  original version is the same, but does not work!
      //  head2 = Table.RenameColumns( head, List.Zip({ Table.ColumnNames(head), cols }) ),
     
        head3 = Table.RemoveColumns( head2, cols{0} ),
        cols2 = Table.ColumnNames( head3 ),
        data2 = Table.RemoveColumns( data, cols2 )
     
    in data2



    • Edited by Bagration.555 Monday, December 9, 2019 11:06 AM readability
    Monday, December 9, 2019 11:05 AM
  • Hi there. Can you share the exact firewall error you get?

    Ehren

    Tuesday, December 10, 2019 11:03 PM
    Owner
  • So I was able to reproduce the error using the M script you shared above. The error is being caused by the way that the firewall is partitioning your query. (Note that the firewall is optimized for the kinds of queries generated by the UI...i.e. queries where each step references the previous one, which is not what you're doing here.)

    Here's how the firewall partitions your query (I've moved some things around for clarity):

    Firewall partitioning diagram

    The "data2" partition is referencing the "cols" and "data" partitions. But it is also invoking get_table (in the head step), which results in a violation of the firewall's rule about "a partition can access data sources or reference other partitions, but not both". When you refactor your query to add some additional steps, this ends up changing how the query is partitioned, which allows it to work.

    My guess is that if you broke this up into multiple queries, it would work just fine.

    Ehren

    Wednesday, December 11, 2019 9:43 PM
    Owner