Forum Discussion
Power Query - Time Intelligence Table - Week Days or working days in Month
So I have a very comprehensive Time Intelligence table at the core of my system which creates dates from 1 July 2019 to 30 June 2030 and has tonnes of columns for fiscal terms.
I have a column that identifies Weekday or Weekend based on the "Date.DayOfWeek([Date])" formula ... returns 0 to 6 depending on date
I have a column that knows DaysInMonth using the "Date.DaysInMonth([Date])" formula ... returns 28,30 or 31 depending on month
I want to exclude weekends from the 2nd formula. Is there is simple formula in Power Query for Weekdays In Month that I don't know about.
I already have about 28 columns in teh Calendar table, so needed something that worked with what was already there ...
Ended up appending this to the existing table ... which has worked ... the below code is from the ADvanced Editor in Power Query. Hopefully this helps someone else. Thx
// Calculate the number of weekdays in each month separately#"Add IsWeekday" = Table.AddColumn(#"Andre-DayNumber", "IsWeekday", each if Date.DayOfWeek([Date], Day.Monday) < 5 then 1 else 0),
#"Filter Weekdays" = Table.SelectRows(#"Add IsWeekday", each [IsWeekday] = 1),
#"Group By Year and Month" = Table.Group(#"Filter Weekdays", {"Year", "MonthNum"}, {{"WeekdaysInMonth", each Table.RowCount(_), Int64.Type}}),
// Merge the weekdays count back into the original table
#"Merged Weekdays" = Table.NestedJoin(#"Andre-DayNumber", {"Year", "MonthNum"}, #"Group By Year and Month", {"Year", "MonthNum"}, "WeekdayCounts", JoinKind.LeftOuter),#"Expanded WeekdayCounts" = Table.ExpandTableColumn(#"Merged Weekdays", "WeekdayCounts", {"WeekdaysInMonth"})
in#"Expanded WeekdayCounts"
2 Replies
In Power Query, you can create a custom column to calculate the number of weekdays in a month by using a combination of existing functions and some custom logic.
Add a Custom Column: First, add a custom column to your table.
Define the Logic: Use the List.Dates function to generate a list of all dates in the month, then filter out the weekends and count the remaining dates.
let
// Extract the year and month from the current date
Year = Date.Year([Date]),
Month = Date.Month([Date]),
// Get the first and last day of the month
StartOfMonth = #date(Year, Month, 1),
EndOfMonth = Date.EndOfMonth(StartOfMonth),
// Generate a list of all dates in the month
AllDatesInMonth = List.Dates(StartOfMonth, Duration.Days(EndOfMonth - StartOfMonth) + 1, #duration(1, 0, 0, 0)),
// Filter out weekends (Saturday = 5, Sunday = 6)
WeekdaysInMonth = List.Select(AllDatesInMonth, each Date.DayOfWeek(_, Day.Sunday) < 5),
// Count the number of weekdays
NumberOfWeekdays = List.Count(WeekdaysInMonth)
in
NumberOfWeekdaysI already have about 28 columns in teh Calendar table, so needed something that worked with what was already there ...
Ended up appending this to the existing table ... which has worked ... the below code is from the ADvanced Editor in Power Query. Hopefully this helps someone else. Thx
// Calculate the number of weekdays in each month separately#"Add IsWeekday" = Table.AddColumn(#"Andre-DayNumber", "IsWeekday", each if Date.DayOfWeek([Date], Day.Monday) < 5 then 1 else 0),
#"Filter Weekdays" = Table.SelectRows(#"Add IsWeekday", each [IsWeekday] = 1),
#"Group By Year and Month" = Table.Group(#"Filter Weekdays", {"Year", "MonthNum"}, {{"WeekdaysInMonth", each Table.RowCount(_), Int64.Type}}),
// Merge the weekdays count back into the original table
#"Merged Weekdays" = Table.NestedJoin(#"Andre-DayNumber", {"Year", "MonthNum"}, #"Group By Year and Month", {"Year", "MonthNum"}, "WeekdayCounts", JoinKind.LeftOuter),#"Expanded WeekdayCounts" = Table.ExpandTableColumn(#"Merged Weekdays", "WeekdayCounts", {"WeekdaysInMonth"})
in#"Expanded WeekdayCounts"
