Custom Function of NetworkDays in Power Query not givng Negative result.

[Anton01]

Good Afternoon, I'm trying to run the following Function, however if the answer is negative it just shows as Error, is there a way to get this to show the actual Negative number instead?
I'm very new to the world of Power Query, so any help is gratefully received.

(StartDate as date, EndDate as date, HolidayList as list) as number =>
let
Weekend = #table(type table[Weekday = Int64.Type],{{0},{6}}),
Holidays = Table.FromColumns({HolidayList},type table[Weekday = date]),
Dates = Table.FromColumns({List.Dates(StartDate,1+Duration.Days(EndDate-StartDate),#duration(1,0,0,0))}, type table[Dates = date]),
#"Inserted Day of Week" = Table.AddColumn(Dates, "Day of Week", each Date.DayOfWeek([Dates]), type number),
#"Merged Queries" = Table.NestedJoin(#"Inserted Day of Week",{"Day of Week"},Weekend,{"Weekday"},"Inserted Day of Week",JoinKind.LeftAnti),
#"Merged Queries1" = Table.NestedJoin(#"Merged Queries",{"Dates"},Holidays,{"Weekday"},"Holidays",JoinKind.LeftAnti),
NetWorkdays = Table.RowCount(#"Merged Queries1")
in
NetWorkdays

 

[JGordon11]

(StartDate as date, EndDate as date, HolidayList as list) as number =>
let
    Weekend = #table(type table[Weekday = Int64.Type],{{0},{6}}),
    Holidays = Table.FromColumns({HolidayList},type table[Weekday = date]),
    m = if EndDate>=StartDate then 1 else -1,
    End = if m=1 then EndDate else StartDate,
    Start = if m =1 then StartDate else EndDate,
    Dates = Table.FromColumns({List.Dates(Start,1+Duration.Days(End-Start),#duration(1,0,0,0))}, type table[Dates = date]),
    #"Inserted Day of Week" = Table.AddColumn(Dates, "Day of Week", each Date.DayOfWeek([Dates]), type number),
    #"Merged Queries" = Table.NestedJoin(#"Inserted Day of Week",{"Day of Week"},Weekend,{"Weekday"},"Inserted Day of Week",JoinKind.LeftAnti),
    #"Merged Queries1" = Table.NestedJoin(#"Merged Queries",{"Dates"},Holidays,{"Weekday"},"Holidays",JoinKind.LeftAnti),
    NetWorkdays = m*Table.RowCount(#"Merged Queries1")
in
NetWorkdays

 

[Anton01]

(StartDate as date, EndDate as date, HolidayList as list) as number =>
let
    Weekend = #table(type table[Weekday = Int64.Type],{{0},{6}}),
    Holidays = Table.FromColumns({HolidayList},type table[Weekday = date]),
    m = if EndDate>=StartDate then 1 else -1,
    End = if m=1 then EndDate else StartDate,
    Start = if m =1 then StartDate else EndDate,
    Dates = Table.FromColumns({List.Dates(Start,1+Duration.Days(End-Start),#duration(1,0,0,0))}, type table[Dates = date]),
    #"Inserted Day of Week" = Table.AddColumn(Dates, "Day of Week", each Date.DayOfWeek([Dates]), type number),
    #"Merged Queries" = Table.NestedJoin(#"Inserted Day of Week",{"Day of Week"},Weekend,{"Weekday"},"Inserted Day of Week",JoinKind.LeftAnti),
    #"Merged Queries1" = Table.NestedJoin(#"Merged Queries",{"Dates"},Holidays,{"Weekday"},"Holidays",JoinKind.LeftAnti),
    NetWorkdays = m*Table.RowCount(#"Merged Queries1")
in
NetWorkdays

Thanks, That works perfectly! exactly what I needed

 

[Anton01]

(StartDate as date, EndDate as date, HolidayList as list) as number =>
let
    Weekend = #table(type table[Weekday = Int64.Type],{{0},{6}}),
    Holidays = Table.FromColumns({HolidayList},type table[Weekday = date]),
    m = if EndDate>=StartDate then 1 else -1,
    End = if m=1 then EndDate else StartDate,
    Start = if m =1 then StartDate else EndDate,
    Dates = Table.FromColumns({List.Dates(Start,1+Duration.Days(End-Start),#duration(1,0,0,0))}, type table[Dates = date]),
    #"Inserted Day of Week" = Table.AddColumn(Dates, "Day of Week", each Date.DayOfWeek([Dates]), type number),
    #"Merged Queries" = Table.NestedJoin(#"Inserted Day of Week",{"Day of Week"},Weekend,{"Weekday"},"Inserted Day of Week",JoinKind.LeftAnti),
    #"Merged Queries1" = Table.NestedJoin(#"Merged Queries",{"Dates"},Holidays,{"Weekday"},"Holidays",JoinKind.LeftAnti),
    NetWorkdays = m*Table.RowCount(#"Merged Queries1")
in
NetWorkdays

I've just realised that if Start Date is the same as End Date it is giving me a result of 1, really it should be 0, , tomorrow should be -1, yesterday should be 1, is there a simple change to the m code to fix this?
Many Thanks
Anton.

 

[JGordon11]

remove the 1+ from this line

Dates = Table.FromColumns({List.Dates(Start,1+Duration.Days(End-Start),#duration(1,0,0,0))}, type table[Dates = date]),

 

[Anton01]

remove the 1+ from this line

Dates = Table.FromColumns({List.Dates(Start,1+Duration.Days(End-Start),#duration(1,0,0,0))}, type table[Dates = date]),

Thanks, That has helped somewhat, however I'm still having another very odd issue, Where I should get a result of -6 i.e. 6 working days until due date, I'm getting -7, Jobs that are due in 7 days are correctly showing as -7, so very odd. Any Ideas?

 

[JGordon11]

Can you post a few start and end date pairs, your holiday list and what the result should be? Something like this (this is with the 1 + removed, and an empty holiday list):

Start	End	NetWorkDays	Right/Wrong?	Result should be?
12/12/2023	12/29/2023	13
11/23/2023	12/12/2023	13
1/3/2024	12/5/2023	-21
11/18/2023	12/24/2023	25
12/31/2023	12/21/2023	-7
12/25/2023	12/29/2023	4
12/2/2023	12/30/2023	20
12/10/2023	11/28/2023	-9
11/26/2023	11/25/2023	-0
11/25/2023	12/23/2023	20
1/1/2024	1/1/2024	0
12/14/2023	11/29/2023	-11
12/28/2023	11/21/2023	-27
12/21/2023	12/11/2023	-8
12/20/2023	12/15/2023	-3
11/19/2023	1/3/2024	32
11/20/2023	12/5/2023	11
12/13/2023	12/24/2023	8
12/7/2023	11/27/2023	-8