Network Days Error in Power Query

[AmritaOS]

Hello,

i am trying to calculate network days from two columns of dates. 

StartDate             EndDate

02/02/2021	02/02/2021
02/02/2021	03/02/2021
02/02/2021	08/02/2021
10/03/2021	19/03/2021
null	20/03/2021
02/02/2021	null
08/02/2021	02/02/2021

conditional logic:

1. take into account weekends and PublicHolidays1

2. If either of the values are null to return a null

3. if the start date is greater than the created date to return a null

here is the function i am using: kindly provided by @Jimmy801 

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

let
IntStartDate = if StartDate = null then null else StartDate ,
IntEndDate = if EndDate = null then null else EndDate ,
ListDates = List.Dates(IntStartDate, Number.From(IntEndDate - IntStartDate), #duration(1, 0, 0, 0)),
RemoveWeekends = List.Select(ListDates, each Date.DayOfWeek(_, Day.Monday) < 5),
RemoveHolidays = List.RemoveItems(RemoveWeekends, PublicHolidays1),
CountDays = List.Count(RemoveHolidays)

in

if IntStartDate > IntEndDate then null else CountDays

i am getting this error and am lost as to how to fix it.

Expression.Error: We cannot convert a value of type Table to type List.
Details:
Value=[Table]
Type=[Type]

thanks

Amrita

[mahoneypat]

I suspect your PublicHolidays1 query is in table form, and a list is expected in the function.  Is that the case?  If so, you can replace it with PublicHolidays1[ColumnName], which will pull that single column (replace with actual name of it) from the PublicHolidays1 table as a list.

Pat

[AmritaOS]

hi pat yes publicholidays1 i have changed the code and its working great - except - i am now gettign this error where the value should return as null

Expression.Error: We cannot convert the value null to type Date.
Details:
Value=
Type=[Type]

[MB_1988]

Hi Amrita, 

I am also getting the same error. Did you manage to resolve it?

[Jimmy801]

Hello @AmritaOS 

do you still have problems?

BR

Jimmy

[AmritaOS · Accepted solution]

HI Jimmy, i used the TRY and removed the error so all good. thanks!