Forum Discussion
Power Query - Time Intelligence Table - Week Days or working days in Month
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"
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
NumberOfWeekdays
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"