Hi.
I'm using Power BI Desktop Jan 2023. I have two queries. One is a function:
()=>
let
Source = Sql.Database("1.2.3.4", "AXDB"),
dbo_INVENTCLOSING = Source{[Schema="dbo",Item="INVENTCLOSING"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_INVENTCLOSING, each ([ACTIVE] = 1) and ([ADJUSTMENTTYPE] = 2) and ([CANCELLATION] = 0) and ([INVENTCOSTSTATUS] = 0) and ([STOPRUNNING] = 0)),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"TRANSDATE", type date}}),
#"TransDate" = #"Changed Type"[TRANSDATE],
TransDate1 = List.Max(#"TransDate")
in
TransDate1
The other is a query in which this function is used:
let
Source = Sql.Database("1.2.3.4", "AXDB"),
dbo_CUSTINVOICETRANS = Source{[Schema="dbo",Item="CUSTINVOICETRANS"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(dbo_CUSTINVOICETRANS,{"INVENTTRANSID", "INVOICEDATE", "ITEMID", "LINEAMOUNT"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"INVOICEDATE", type date}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each [INVOICEDATE] <= LastClosing()),
#"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"INVENTTRANSID"}, INVENTTRANSORIGIN, {"INVENTTRANSID"}, "INVENTTRANSORIGIN", JoinKind.Inner),
#"Expanded INVENTTRANSORIGIN" = Table.ExpandTableColumn(#"Merged Queries", "INVENTTRANSORIGIN", {"SALESID", "CostAmount"}, {"SALESID", "CostAmount"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded INVENTTRANSORIGIN",{"INVENTTRANSID"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Margin", each [LINEAMOUNT] + [CostAmount]),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Margin", type number}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"CostAmount"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"SALESID", "ITEMID", "INVOICEDATE", "LINEAMOUNT", "Margin"})
in
#"Reordered Columns"
In this query, when I reach "Merged Queries" step, I receive this error:
Formula.Firewall: Query 'CUSTINVOICETRANS' (step 'Merged Queries') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
When I move "Filtered Rows" to last step, I receive the (almost) same error on last step (Not on "Merged Queries" step). This is the modified query:
let
Source = Sql.Database("1.2.3.4", "AXDB"),
dbo_CUSTINVOICETRANS = Source{[Schema="dbo",Item="CUSTINVOICETRANS"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(dbo_CUSTINVOICETRANS,{"INVENTTRANSID", "INVOICEDATE", "ITEMID", "LINEAMOUNT"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"INVOICEDATE", type date}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"INVENTTRANSID"}, INVENTTRANSORIGIN, {"INVENTTRANSID"}, "INVENTTRANSORIGIN", JoinKind.Inner),
#"Expanded INVENTTRANSORIGIN" = Table.ExpandTableColumn(#"Merged Queries", "INVENTTRANSORIGIN", {"SALESID", "CostAmount"}, {"SALESID", "CostAmount"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded INVENTTRANSORIGIN",{"INVENTTRANSID"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Margin", each [LINEAMOUNT] + [CostAmount]),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Margin", type number}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"CostAmount"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"SALESID", "ITEMID", "INVOICEDATE", "LINEAMOUNT", "Margin"}),
#"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each [INVOICEDATE] <= LastClosing())
in
#"Filtered Rows"
And this is the error:
Formula.Firewall: Query 'CUSTINVOICETRANS' (step 'Filtered Rows') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
All of my queries come from a single data source (D365FFO). I have done the same thing in another report with another database (and that report contains more complex queries using the same function, compared to the above). What's wrong?
Thanks.
