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.
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.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.