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.
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.
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.
Hi boolittlek ,
Thank you for confirming the behavior. According to your latest test, the privacy or firewall error is still occurring because Power Query detects an indirect reference to another query, even though the function works as expected on its own.
Your approach of combining the SharePoint holidays and API pagination within the same query is effective and works in both Desktop and the Service, so you can continue with this method confidently.
Alternatively, using a DAX measure for the delta calculation is a good option if you only need the result at the reporting level.
Regards,
Yugandhar.