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!