Extract week days of a month from a date

[cho]

Hi!

In power Query, I need to extract number of working days for a month. But the table I have has the dates.

Dates

1/2/2022

1/3/2022

1/9/2022

1/19/2022

1/25/2022

Since there are 20 weekdays for January 2022, my table should look like this.

Dates	Weekdays
1/2/2022	20
1/3/2022	20
1/9/2022	20
1/19/2022	20
1/25/2022	20

I do not mind additional columns. I tried adding two columns and writing a fuction.

Two columns:

StartD = Date.StartOfMonth([DATE])

EndD = Date.EndOfMonth([DATE])

Function:

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

But values for weekdays appears to be wrong for some months.

I tried adding as:

RemoveWeekends = List.Select(ListDates,each Date.DayOfWeek(_,Day.Monday)+1 <=5 )

But results are still wrong.

Please help 😞

[ppm1 · Accepted solution]

You likely need to add 1 to your count in List.Dates.

Number.From(EndDate-StartDate) + 1

Pat