Formula.Firewall error help

[Anonymous]

Hi there,

I recently encountered the Formula Firewall error for the first time and I do not full understand how to solve it. And from what I can see in the community posts it looks like that many have the same problem. I created a toy dataset which genrates this error in two different cases. I can solve the problem in case 1 but not in case 2 and I hope the community can help out here.

Ken Puls has a tutorial in which he creates a "data stage" by moving the external data source into an extra query.  I do this in case 1 and in case 2 but it doesn't work in the later case. What is the correct way of doing this? 

Objective:

I want to apply a set of transformation steps to multiple files in a folder using a custom function. The example data folder has 3 tables (Good_Luck_Level, Formula_Firewall_Table_r1 (r1),Formula_Firewall_Table_r2 (r2) ) and I want to write a function that merges Good_Luck_Level with one of the Formula_Firewall_Table_r* tables depending on which index is given to the function as input. 

The data can be downloaded here .

The query organisation looks like this. Each case has a problem and a solution or attempt group. The logical order for these queries is top to down. 

You have to add the path to the data folder as a parameter for it to work.

This is what the tables look like (r2 is very similar to r1 snd is not shown here). 

Formula_Firewall_r1Good Luck Level

As I said I get the Formula Firewall error on 2 occasions.

Case 1

Using Table.ExpandColumns gives me the error

(idx as number,data_path as text) =>
// idx: index of files in directory 
// data_path: path to files that are to be loaded
let
    Source = Folder.Files(data_path),
    
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], 
    "Formula_Firewall_Table_r") and Text.Contains([Name], "")),
    
    #"Gefilterte Zeilen1" = Table.SelectRows(#"Filtered Rows", each [Date created] = #"Filtered 
    Rows"[Date created]{idx}),
    
    #"Filtered Hidden Files1" = Table.SelectRows(#"Gefilterte Zeilen1", each [Attributes]? 
    [Hidden]? <> true),
    
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", 
    each #"Transform File"([Content])),
    
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", 
    "Source.Name"}),
    
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", 
    "Transform File"}),

    // This steps generates an error
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform 
    File", Table.ColumnNames(#"Transform File"(#"Sample File")))

in
    #"Expanded Table Column1"

I can solve the first problem by moving Table.ExpandColumns into another function (expand_table_dyn).

#"Expanded Table Column1" =expand_table_dyn(#"Removed Other Columns1","Transform File")
    

Case 2

After expanding the table, I want to join it with the Good Luck Level table.

(idx as number,data_path as text) =>
// idx: index of files in directory 
// data_path: path to files that are to be loaded
let
    Source = Folder.Files(data_path),
    
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], 
    "Formula_Firewall_Table_r") and Text.Contains([Name], "")),
    
    #"Gefilterte Zeilen1" = Table.SelectRows(#"Filtered Rows", each [Date created] = #"Filtered 
    Rows"[Date created]{idx}),
    
    #"Filtered Hidden Files1" = Table.SelectRows(#"Gefilterte Zeilen1", each [Attributes]? 
    [Hidden]? <> true),
    
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", 
    each #"Transform File"([Content])),
    
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", 
    "Source.Name"}),
    
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", 
    "Transform File"}),

    // This steps generates an error
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform 
    File", Table.ColumnNames(#"Transform File"(#"Sample File")))

    #"Merged Queries" = Table.NestedJoin(#"Expanded Table Column1", {"Town"}, #"staging Good 
    Luck Level", {"Town"}, "Good Luck Level", JoinKind.LeftOuter)
in
    #"Merged Queries"

Attempt1 

I tried moving Table.NestedJoin into a seperate function "merge"

= (t1 as table, merge_on1 as text,t2 as table, merge_on2 as text, name as text) =>
let
    Source =  Table.NestedJoin(t1, {merge_on1}, 2, {merge_on2}, name, JoinKind.LeftOuter)
in
    Source

and Attempt2

Moving Table.NestedJoin into a seperate function and saving it as a new table

= (t1 as table, merge_on1 as text,t2 as table, merge_on2 as text, name as text) =>
let
    Source =  Table.NestedJoin(t1, merge_on1, t2, merge_on2, name, JoinKind.LeftOuter),
    #"Expanded something" = expand_table_dyn(Source,name),
    out = Table.SelectColumns(#"Expanded something",Table.ColumnNames(#"Expanded something"))
in
    out

Really appreciate your help and thoughts here.

[Anonymous]

Hi @Anonymous ,

Please review the following links which have the similar problem as yours, hope their solution can help you resolve the problem.

Formula.Firewall Error Referencing Queries

Method 1: Ignore Privacy Levels

Method 2: Use a Function

---

Data Privacy Settings In Power BI/Power Query, Part 3: The Formula.Firewall Error

Formula.Firewall: Query references other queries, so it may not directly access a data source

---

Yes I was able to get around it just now by effectively moving all of the steps from the other query into the same query. This gets around the problem but it's a bit annoying.

---

Best Regards

[Anonymous]

Hi Rena,

thanks for your reply. Method 1 is sadly not an option for me and Method 2 is what I tried and I am struggling with. That's why I created an toy example which can be found here https://wetransfer.com/downloads/59020a9bfec7c8703f588f6540f9d3b520220527121004/fecfc0 .

Any suggestions how to fix this? 

All the best,

Myagi-Do