The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to 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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
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.
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
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
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
Hi @Lucian ,
thanky you 🙂
Use the parameters in the individual tables.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
95 | |
80 | |
62 | |
56 |
User | Count |
---|---|
256 | |
120 | |
112 | |
83 | |
71 |