Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
cho
Helper I
Helper I

Extract week days of a month from a date

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.

DatesWeekdays

1/2/2022

20

1/3/202220
1/9/202220
1/19/202220
1/25/202220

 

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 😞

1 ACCEPTED SOLUTION
ppm1
Solution Sage
Solution Sage

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

 

Number.From(EndDate-StartDate) + 1

 

Pat

Microsoft Employee

View solution in original post

1 REPLY 1
ppm1
Solution Sage
Solution Sage

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

 

Number.From(EndDate-StartDate) + 1

 

Pat

Microsoft Employee

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors