Power Query: week of the month
Timesheets!
At my current company, part of my job is analysing timesheet data. The data that comes from the timesheeting system has an interesting date format. For example, it will express dates like "Jul-01-2022", to mean "July, Week 1, 2022".
Now, July, Week 1, 2022 might make sense for a person, but if we want Power BI to make sense of the actual time of that, we'll need to convert that to an actual date.
The script below generates a table of weekdays, with a column that tells you which "week of the month" it falls in. It also has the day of the week. In my case, I needed to filter for just the Fridays to get the dates I need. You can do the same.
let
year = 2020,
thisYear = Date.Year(DateTime.LocalNow()),
thisMonth = Date.Month(DateTime.LocalNow()),
endYear = if (List.Contains({7..12}, thisMonth)) then thisYear + 1 else thisYear,
// start and end dates of the Australian financial year
startDate = #date(year, 07, 01),
endDate = #date(endYear, 06, 30),
days = Duration.Days(endDate - startDate),
// seed numbers
numbers = {0..days},
dates = List.Transform(
numbers,
each Date.AddDays(startDate, _)
),
// clamp list of dates to fit within this financial year
thisFinancialYear = List.Select(
dates,
each _ >= startDate and _ <= endDate
),
// tabulate
dateTable = Table.FromColumns(
{thisFinancialYear},
{"Date"}
),
#"Changed Type" = Table.TransformColumnTypes(dateTable,{{"Date", type date}}),
addDays = Table.AddColumn(#"Changed Type", "Day of Week", each Text.Start(Date.DayOfWeekName([Date]), 3), type text),
// take out weekends - this messes up week numbers
fridaysOnly = Table.SelectRows(
addDays,
each [Day of Week] = "Fri" // not List.Contains({"Sat", "Sun"}, [Day of Week])
),
addMonthNumber = Table.AddColumn(fridaysOnly, "Month", each Date.Month([Date]), Int8.Type),
addMonthName = Table.AddColumn(addMonthNumber, "Month Name", each Date.MonthName([Date]), type text),
addYear = Table.AddColumn(addMonthName, "Year", each Date.Year([Date]), Int16.Type),
addFinancialYear = Table.AddColumn(addYear, "Financial Year", each if List.Contains({7..12}, [Month]) then [Year] + 1 else [Year], Int16.Type),
addWeekNumber = Table.AddColumn(addFinancialYear, "Week Number", each Date.WeekOfYear([Date]), Int8.Type),
// return to this later
// make a reference table for which months contain which weeks
groupByMonth = Table.Group(
addWeekNumber,
{"Year", "Month"},
{
{
"Week Numbers",
each [Week Number],
Int8.Type
}
}
),
monthStartAndEndWeeks = Table.TransformColumns(
groupByMonth,
{
{
"Week Numbers",
each [
#"Start Week" = List.First(_),
#"End Week" = List.Last(_)
],
type record
}
}
),
weekReference = Table.Buffer(
Table.ExpandRecordColumn(
monthStartAndEndWeeks,
"Week Numbers",
{"Start Week", "End Week"},
{"Start Week", "End Week"}
)
),
// return to week by week
weekOfMonth = Table.AddColumn(
addWeekNumber,
"Week Number of Month",
each
let
thisMonth = [Month],
thisYear = [Year]
in
[Week Number] - weekReference{[Year = thisYear, Month = thisMonth]}[Start Week] + 1
),
addProjectPeriodName = Table.AddColumn(
weekOfMonth,
"Project Period Name",
each Text.Combine({
Text.Start([Month Name], 3),
Number.ToText([Week Number of Month], "00"),
Text.End(Text.From([Year]), 2)
},
"-"
),
type text
)
in
addProjectPeriodName