Results 1 to 2 of 2

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

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Neophyte Se7enSquared's Avatar
    Join Date
    Apr 2019
    Posts
    2
    Articles
    0
    Excel Version
    Office 365 / 2016

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

    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!

  2. #2
    Neophyte Se7enSquared's Avatar
    Join Date
    Apr 2019
    Posts
    2
    Articles
    0
    Excel Version
    Office 365 / 2016
    FYI, I fixed this myself by using a parameter instead.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •