Expression.Error: A cyclic reference was encountered during evaluation.

I have created a custom function from a blank query to calculate the number of days excluding holidays and the weekends, the function is below

   Query1 = (StartDate as date, EndDate as date) as number => 

let 
DateList = List.Dates(StartDate, Number.From(EndDate - StartDate) , #duration(1, 0, 0, 0)),
RemoveWeekends = List.Select(DateList, each Date.DayOfWeek(_, Day.Monday) < 5),
RemoveHolidays = List.RemoveItems(RemoveWeekends, Holidays),
CountDays = List.Count(RemoveHolidays)
in
CountDays,
   Custom1 = Query1
in
   Custom1

Everything is working in the file on my computer, but the issue is when anyone else from my team is trying to use the file with queries there. We are getting error <Expression.Error: A cyclic reference was encountered during evaluation.>

I've tried to trace steps and find out when the error is occurring and it's always pointing at the custom function. Please help me, I need to fix it asap so my team can use this.

Function screen

Calendar before function

Calendar after function

I was using this youtube guide to create function: https://www.youtube.com/watch?v=e2ic432NvhY

CodePudding user response：

try changing your function to

(StartDate as date, EndDate as date) as number => 
let DateList = List.Dates(StartDate, Number.From(EndDate - StartDate) , #duration(1, 0, 0, 0)),
RemoveWeekends = List.Select(DateList, each Date.DayOfWeek(_, Day.Monday) < 5),
RemoveHolidays = List.RemoveItems(RemoveWeekends, Holidays),
CountDays = List.Count(RemoveHolidays)
in CountDays

then, assuming Holidays is a query with some list of dates like

= {#date(2022,6,15),#date(2022,1,18)}

then

= Table.AddColumn(#"Changed Type", "Custom", each NetWorkDays([StartDate],[EndDate]))

works fine

CodePudding user response：

Thanks everyone for such quick answers. My issue was that first I've created custom function based on list of holidays and calendar and then i've used it in calendar as well. When I removed custom function from the calendar query, leaving only starting date of the week, ending date and week number it started to work again.