Expression.Error: A cyclic reference was encountered during evaluation. - using custom function NetWorkDays in Power Query

Question

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

I have four columns, one with work week number, next is week starting date, ending date and the last one is with bank holidays. — MaydayMayday, Jun 21 '22 at 07:39

horseyride · Answer 1 · 2022-06-20T15:06:25.373

0

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

edited Jun 20 '22 at 15:06

answered Jun 20 '22 at 14:11

horseyride

17,007
2
11
22

Unfortunately this does not work. – MaydayMayday Jun 20 '22 at 14:52
I put in the full code for the function, including removing the beginning. Try now. Works for me – horseyride Jun 20 '22 at 15:06
Holidays is a query with workweeks and holiday dates. I've tried to change the code but does not work, I am getting the same error:( – MaydayMayday Jun 21 '22 at 07:36
Holidays needs to be a list of dates. Then the above code works perfectly. Can't help beyond that. See if you code works without the **,Holidays** part. If so, that is the issue – horseyride Jun 21 '22 at 09:04

score 0 · Answer 2 · answered Jun 23 '22 at 08:25

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.

Expression.Error: A cyclic reference was encountered during evaluation. - using custom function NetWorkDays in Power Query

2 Answers2