Formula.Firewall Error in combination with PowerQuery function

[Lucian]

Hello,

I am trying to create a "dynamic union" of identical tables from a Navision database.  Basicaly I need come "consolidated data" without using the Consolidated company from NAV.

These tables are using the followig rule: CompanyName$TableName. 

I have created a PowerQuery function (GetProjectsTable) that will load for example the Projects table (the same principle will be used for other tables too):

(strCompanyName as text) as table =>
let
    strTableName=strCompanyName&"$Projects",
    Source = Sql.Databases(NAVDataURL),
    srcDBName = Source{[Name=NAVDBName]}[Data],
    #"tblProjects" = srcDBName{[Schema="dbo",Item=strTableName]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(#"tblProjects",{"Code", "Description", "Starting date", "Ending Date", "Sales person Code", "Project Type"})
in
    #"Removed Other Columns"

With another query (Company) I get the company names for which I will need the "consolidated tables" (similar with union):

let
    Source = Sql.Databases(NAVDataURL),
    DBSource = Source{[Name=NAVDBName]}[Data],
    dbo_Company = DBSource{[Schema="dbo",Item="Company"]}[Data],
    #"Removed Columns" = Table.RemoveColumns(dbo_Company,{"timestamp", "Evaluation Company"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Name] = "Comp1" or [Name] = "Comp2"))
in
    #"Filtered Rows"

This query will return a single column table:

Comp1

Comp2

To create the ALLProjects table I would like to "reference" directly the Company query because this would be filtered depending on some criteria and the same list would be used to generate an ALLTableName. Based on the referenced query, I add a new column invoking the custom function that should return the proper table for each company.

So I have tryied this query:

let
    Source = Company,
    #"Invoked Custom Function" = Table.AddColumn(Source, "GetProjectsTable", each GetProjectsTable([Name]))
in
    #"Invoked Custom Function"

But this approach would result in the following error:

Formula.Firewall: Query 'Company (2)' (step 'Invoked Custom Function') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

Another approach was to "duplicate" the Company query - this way is more like an "independent copy" and now adding the column invoking the custom function to get the tables will work.

let
    Source = Sql.Databases(NAVDataURL),
    DBSource = Source{[Name=NAVDBName]}[Data],
    dbo_Company = DBSource{[Schema="dbo",Item="Company"]}[Data],
    #"Removed Columns" = Table.RemoveColumns(dbo_Company,{"timestamp", "Evaluation Company"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Name] = "Comp1" or [Name] = "Comp2")),
    #"Invoked Custom Function" = Table.AddColumn(#"Filtered Rows", "GetProjectsTable", each GetProjectsTable([Name])),
    #"Expanded GetProjectsTable" = Table.ExpandTableColumn(#"Invoked Custom Function", "GetProjectsTable", {"Code", "Description", "Starting date", "Ending Date", "Sales person Code", "Project Type"}, {"Code", "Description", "Starting date", "Ending Date", "Sales person Code", "Project Type"})
in
    #"Expanded GetProjectsTable"

But using this approach if the filter would change, I have to manually change each query that would get the tables.

Is there a way to get a "list" that could be "referenced" in other queries that will invoke some functions that return tables that would be expanded without bumping into "Formula.Firewall Error"?...

Kind Regards,

Lucian

[Greg_Deckler]

@ImkeF 

[Lucian]

Hi @Greg_Deckler ,

I would like to thank you for the replies on other posts... but for this one I have a problem reading it... 😁

Could you please post it again?

Kind Regards,

Lucian

[Greg_Deckler]

Oh, sorry, that is probably because that post was really, really short. I was just "invoking @ImkeF ". She is one of, if not the best, Power Query person out there so when I find a Power Query question that I can't answer, I call her attention to it by mentioning her.

[Lucian]

Oh, thank you @Greg_Deckler  for clarifications, and until @ImkeF would have a chance to respond, I would do a quick update:

The trick with "Ignoring the Privacy Levels" for the current file from here:  Data Privacy and the Formula Firewall in Power BI / Power Query - will help me avoid the error on the desktop but publishing the report will bring it back on the PBI service.

Also the "staging" queries from here: Power Query Errors: Please Rebuild This Data Combination did not work either, or did not know how use it correctly.

Any other ideeas?

Kind Regards,

Lucian

[ImkeF · Accepted solution]

try to "functionize" the Company-query like so: (adding " () => " at the start)

() =>
let
    Source = Sql.Databases(NAVDataURL),
    DBSource = Source{[Name=NAVDBName]}[Data],
    dbo_Company = DBSource{[Schema="dbo",Item="Company"]}[Data],
    #"Removed Columns" = Table.RemoveColumns(dbo_Company,{"timestamp", "Evaluation Company"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Name] = "Comp1" or [Name] = "Comp2"))
in
    #"Filtered Rows"

and then call it like so: Company() 
This is a function without a parameter, but you have to include the parenthesis.

let
    Source = Company(),
    #"Invoked Custom Function" = Table.AddColumn(Source, "GetProjectsTable", each GetProjectsTable([Name]))
in
    #"Invoked Custom Function"

[Anonymous]

Thanks much. Your tip worked fine for a similar issue I was facing. I was trying to get a sharepoint list in query1, which I use as input for a custom function(which was calling another API). The privacy levels for query1 and the API were at 'Organization' level, but still I was getting the Formula.Firewall error. 

After 'functionizing' query1 it worked just perfectly.

[Lucian]

Hello @ImkeF ,

Thank you veeeery much for your time and especially for this "quick fix". 😀 

And many thanks to @Greg_Deckler for bringing my problem to your attention, so I've solved my problem very quickly.

I could only found a solution, by myself, by creating a manual list of values: {"Comp1", "Comp2"} that was used into the functions that get the table data in the "Filtered Rows" so I do not have to edit again every query.

Still your solution is better, because I could use the "original" query with just a single line! Wow! And it worked! 😁

One more question related to this problem: Is there any chance to benefit from the "Incremental Refresh" recently launched to Power BI Pro users? In this case where should I use the specific parameters RangeStart/RangeEnd - inside the query/function that generate the individual tables or in the final "consolidated" table?

Kind Regards,

Lucian

[ImkeF]

Hi @Lucian  ,

thanky you 🙂

Use the parameters in the individual tables.

[Lucian]

Hi @ImkeF 

I will try your suggestion. Thank you again for helping me.

Kind Regards,

Lucian