Forum Discussion
Query references other queries or steps, so it may not directly access a data source
I tried removing the holiday component as a parameter in the function and hard-coding a reference to the list object:
I tested it, and the function itself works--I was able to get the correct values by inputting test start and end dates. However, when I use the function in my main query (again, passing only the start and end dates), I still get the "references other queries" error.
I may have to just stick with the combo method (calling the SharePoint list of holidays directly in my main query) or try calculating the delta in a DAX measure.
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
In a separate query Holidays, keep only the HolidayDate column and turn it into a list: HolidaysList = List.Buffer(Holidays[HolidayDate]).
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).
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.
I'm able to create the list (Option 1, Step 1), but I'm unclear what needs to change in the new fnNetworkDays function (Option 1, Step 2).
I was able to put to put my code for the Holidays table (which I extract from SharePoint) within my pagination query (that pulls in data from the GetDataProjectTiming function). I set both data sources to the Organizational privacy setting, and this appears to be working in both Desktop and the Online Service (I did not have to use the 'Ignore privacy levels' setting).
Thank you for the update.
For Option 1, the key point is that the custom fnNetworkDays function just needs an additional parameter for your Holidays list. Once that's included, the function should use this list internally, rather than referencing the Holidays query directly. This helps avoid the privacy or firewall issue.
Your current method bringing the Holidays data into the same query as your API pagination and setting both to the Organizational privacy level also works well. Since it's functioning in both Desktop and the Service, you're in a good position. Option 1 is just a cleaner and more reusable approach if you want a solution that works independently across queries.
I hope this helps clarify things. If you have any further questions, feel free to ask.
Sorry, I'm still not fully understanding. Am I supposed to create a parameter (populated by the list query I created in step 1) and then reference this parameter in the function code? I'm not exactly sure how to do this, and I also don't know what to invoke in the main query (for the holiday component) when I'm actually using the function--it keeps throwing the same error.
Hi boolittlek ,
You don’t need to create a Power Query parameter in the UI. In this case, parameter just refers to adding another input argument to your fnNetworkDays function.So, 1. You’ve already made your HolidaysList (a list of dates).
2. Your fnNetworkDays function should accept that list as its third argument.
3. In your main query, call the function with StartDate, EndDate, and HolidaysList.
This way, the function uses the values you pass to it instead of referencing the Holidays query directly, which avoids the privacy/firewall error.
Regards,
Yugandhar.
