"Formula.Firewall" issue. Usual solution does not seem to help?

Se7enSquared

New member
Joined
Apr 19, 2019
Messages
2
Reaction score
0
Points
0
Excel Version(s)
Office 365 / 2016
ERROR: Formula.Firewall: Query 'qryLTBbyLPODate_3d' (step 'Filter: LPO Date >= today') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
In my code, the first line and the last line before "In" are the conflicts.
I have read about this error and it says do not directly access the data source, such as from an excel table or a database and shows the solution to be accessing via an intermediate query instead. But that seems to be exactly what I'm doing here... I created a custom function to get the value from a named range and passing in the range to a cell that always has today's date. Then attempting to filter based on that value.


Here's the Power Query code (notice I am referencing another Power Query, not a direct data source):
let
Source = qryPartDataLatestEOL_3,
#"Removed Other Columns" = Table.SelectColumns(Source,{"ComponentType", "PartNumber", "SAPDescription", "BLFDate", "LPODate", "SLSDate", "Program"}),
#"Filter: Show LPO < 7/1/2020 or blank" = Table.SelectRows(#"Removed Other Columns", each [LPODate] < #date(2020, 7, 1)),
#"Removed Duplicate Parts" = Table.Distinct(#"Filter: Show LPO < 7/1/2020 or blank", {"PartNumber"}),
#"Condition: If LCE, show only if SLS < 10/1/2019" = Table.AddColumn(#"Removed Duplicate Parts", "Include?", each if [Program] = "LLP" then 1 else if [LPODate] < #date(2019, 10, 1) then 1 else 0),
#"Filter: Blank SLS Dates" = Table.SelectRows(#"Condition: If LCE, show only if SLS < 10/1/2019", each ([LPODate] <> null)),
#"Sort: Part Number" = Table.Sort(#"Filter: Blank SLS Dates",{{"PartNumber", Order.Ascending}}),
#"Filter: Only show parts to include" = Table.SelectRows(#"Sort: Part Number", each ([#"Include?"] = 1)),
#"Remove: Include? column" = Table.RemoveColumns(#"Filter: Only show parts to include",{"Include?"}),
#"Reorder: Move Program column to beginning" = Table.ReorderColumns(#"Remove: Include? column",{"Program", "ComponentType", "PartNumber", "SAPDescription", "BLFDate", "LPODate", "SLSDate"}),
#"Filter: LPO Date >= today" = Table.SelectRows(#"Reorder: Move Program column to beginning", each ([LPODate] < funGetNamedRangeValue("refTodaysDate")))
in
#"Filter: LPO Date >= today"


As usual, thanks for the help! :)
 
FYI, I fixed this myself by using a parameter instead. :)
 
Back
Top