Query references other queries or steps, so it may not directly access a data source

Hi, boolittlek 

The Formula.Firewall error typically occurs when you mix a streaming data source (like your List.Generate API pagination) with another external data source (your Holidays table) without buffering the static data first. The engine gets confused about data privacy partition.

I have refactored your code below to do two things:

1. List.Buffer: I loaded the Holidays list into memory before the API call loop starts. This treats the holidays as a constant list, usually resolving the Firewall partition issue.

2. Clean Up: I removed spaces and special characters from the step names (e.g., changing #"Added Custom" to AddedCustom). This is a best practice in M to make the code more stable and readable.

Here is the refactored code:

let


  // 1. Buffer the Holidays list into memory FIRST to avoid Firewall errors
  // Assuming 'Holidays' is your table and 'HolidayDate' is the column name containing dates
  HolidaysList = List.Buffer(Holidays[HolidayDate]),
  // 2. Your API pagination
  Source = List.Generate(
    () => [Result = try GetDataProjectTiming(0) otherwise null, Offset = 0],
    each List.IsEmpty([Result][data]) <> true,
    each [
      Result = try GetDataProjectTiming([Offset] + 2000) otherwise null,
      Offset = [Offset] + 2000
    ],
    each [Result]
  ),
  // 3. Transformation Steps (Renamed to remove spaces/special chars)
  ConvertedToTable = Table.FromList(
    Source,
    Splitter.SplitByNothing(),
    null,
    null,
    ExtraValues.Error
  ),
  ExpandedColumn1 = Table.ExpandRecordColumn(ConvertedToTable, "Column1", {"data"}, {"data"}),
  ExpandedData = Table.ExpandListColumn(ExpandedColumn1, "data"),
  ExpandedDataRecords = Table.ExpandRecordColumn(
    ExpandedData,
    "data",
    {
      "ID",
      "name",
      "owner",
      "referenceNumber",
      "actualCompletionDate",
      "DE:Requested completion date",
      "DE:Project Timing",
      "DE:Proposition this request falls under."
    },
    {
      "ID",
      "name",
      "owner",
      "referenceNumber",
      "actualCompletionDate",
      "DE:Requested completion date",
      "DE:Project Timing",
      "DE:Proposition this request falls under."
    }
  ),
  ExpandedOwner = Table.ExpandRecordColumn(ExpandedDataRecords, "owner", {"name"}, {"name.1"}),
  RenamedColumns = Table.RenameColumns(
    ExpandedOwner,
    {
      {"ID", "ProjectID"},
      {"name", "ProjectName"},
      {"name.1", "OwnerName"},
      {"referenceNumber", "ReferenceNumber"},
      {"actualCompletionDate", "actualCompletionDateOLD"},
      {"DE:Requested completion date", "RequestedCompletionDate"},
      {"DE:Project Timing", "ProjectTiming"},
      {"DE:Proposition this request falls under.", "Proposition"}
    }
  ),
  // Clean text range
  AddedCustom = Table.AddColumn(
    RenamedColumns,
    "ActualCompletionDateClean",
    each Text.RemoveRange([actualCompletionDateOLD], 19, 4)
  ),
  ChangedType = Table.TransformColumnTypes(
    AddedCustom,
    {{"ActualCompletionDateClean", type datetimezone}}
  ),
  ChangedTypeFinal = Table.TransformColumnTypes(
    ChangedType,
    {
      {"ActualCompletionDateClean", type date},
      {"ProjectID", type text},
      {"ProjectName", type text},
      {"OwnerName", type text},
      {"ReferenceNumber", Int64.Type},
      {"RequestedCompletionDate", type date},
      {"ProjectTiming", type text},
      {"Proposition", type text}
    }
  ),
  RemovedColumns = Table.RemoveColumns(ChangedTypeFinal, {"actualCompletionDateOLD"}),
  // Calculate Delta using the Buffered Holidays List
  AddedDelta = Table.AddColumn(
    RemovedColumns,
    "Delta",
    each NetworkDays([ActualCompletionDateClean], [RequestedCompletionDate], HolidaysList)
  )
in
  AddedDelta

Note on Privacy Settings: You might find that going to File > Options and settings > Options > Privacy and selecting "Ignore the Privacy Levels" fixes the error immediately.

However, be careful with that approach: it disables important security checks and might cause refresh failures once published to the Power BI Service (Gateway) if the server settings don't match. The code fix above (List.Buffer) is the robust way to solve it ensuring your refresh works everywhere.

Let me know if this helps!

This error is from the privacy/firewall rules: in one query you both call an API (GetDataProjectTiming) and reference another query/table (Holidays), which is not allowed in that partition.​

Option 1 – Turn holidays into a list parameter

1. In a separate query Holidays, keep only the HolidayDate column and turn it into a list: HolidaysList = List.Buffer(Holidays[HolidayDate]).​

2. Create a new function fnNetworkDays that takes StartDate, EndDate and HolidaysList as parameters (copy Imke’s function, but use a parameter instead of referencing the Holidays query).​

3. In ProjectTiming use:

    

   #"Added Custom1" = Table.AddColumn( #"Removed Columns", "Delta", each fnNetworkDays([ActualCompletionDate], [RequestedCompletionDate], HolidaysList) )

   Now the query only calls a function and does not directly reference the Holidays query, so the firewall is satisfied.​

Option 2 – Put everything in one query (quick and dirty)

If this is for personal use only, you can instead set Current File ➜ Privacy to “Ignore the privacy levels and potentially improve performance” and refresh again.​​
This disables the firewall check, but is not recommended for shared or sensitive data models.

Hi boolittlek ,

Just checking in are you still experiencing any issues? If you need more information or clarification, please let us know.

Thank You.

Hi boolittlek ,

May I know if your issue is resolved? If you need any additional help or further details, please let us know.

Thank you.

Thank you for sharing your approach.