ML with circles

Mearns Learns

Create a calendar table

Using M code a date table can be created based off a start and end date.

Based off the start and end date the following columns are created:

  • Date
  • Year
  • Month No.
  • Month
  • Month Short
  • YYYY-MM
  • End Of Month
  • Weekend
  • Standard Hours
  • MMM-YYYY
  • Index

More columns can be added depending on the requirements of the report.
In this example a Standard Hours column has been added.

Static Start Date

In the first example, the start date is hard coded. The end date is set to the last day of the current month using a calculation.

let
  // Hard coded value
  Start_Date = #date(2021, 1, 1),
  // Today's date obtained via formula and converted to the last day of the month
 End_Date = Date.EndOfMonth(Date.From(DateTime.LocalNow())),
  // Create a list of dates by day between the start and end date
 Source = List.Dates(
        Start_Date,
        Duration.Days(End_Date-Start_Date)+1,
        #duration(1, 0, 0, 0)),
  // Convert the list to a table, setting column name and format to Date
  #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(),type table[Date=Date.Type], null, ExtraValues.Error),
  // Add year column
  #"Extract Year" = Table.AddColumn(#"Converted to Table", "Year", each Number.ToText(Date.Year([Date])), type text),
  // Add month number column
  #"Extract Month No." = Table.AddColumn(#"Extract Year", "Month No.", each Number.ToText(Date.Month([Date]),"D2"), type text),
  // Add month name column
  #"Extract Month Name" = Table.AddColumn(#"Extract Month No.", "Month", each Date.MonthName([Date]), type text),
  // Add month short name column
  #"Extract Month short name" = Table.AddColumn(#"Extract Month Name", "Month Short", each Text.Start([Month], 3), type text),
  // Add year-month number column
  #"Create YYYY-MM" = Table.AddColumn(#"Extract Month short name", "YYYY-MM", each Text.Combine({[Year], [#"Month No."]}, "-"), type text),
  // Add end of month column
  #"Calculate EoM date" = Table.AddColumn(#"Create YYYY-MM", "End Of Month", each Date.EndOfMonth([Date]), type date),
  // Add column that tests date to see if it is a weekend
  #"Is weekend" = Table.AddColumn(#"Calculate EoM date", "Weekend", each if Date.DayOfWeek([Date])=5 or Date.DayOfWeek([Date])=6 then 1 else 0, Int64.Type),
  // Add a column for eight standard hours per week day
  #"Calculate Standard hours" = Table.AddColumn(#"Is weekend", "Standard Hours", each if [Weekend] = 1 then 0 else 8, type number),
  // Add a column with short month- year
  #"Create MMM-YYYY" = Table.AddColumn(#"Calculate Standard hours", "MMM-YYYY", each Text.Combine({[Month Short], [Year]}, "-"), type text),
  // Sort rows
  #"Sorted Rows" = Table.Sort(#"Create MMM-YYYY", {{"Date", Order.Ascending}}),
  // Add an index column
  #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type)
in
    #"Added Index"

Dynamic Start Date

A query needs to be created that returns a list with a single item.
Use List.Min to get the first date from a date column in the transactional data.
This query has been called FirstDate.

let
    Source = List.Min(#"Transaction Dates"[Dates])
in
    Source

Then feed the result of FirstDate into the Start_Date variable of the M code that creates the Calendar table.
The rest of the code is the same as the static example.

let
  // Dynamic single value date from source data
  // Converted to first day of month, could use Date.StartOfYear or just the date
 Start_Date = Date.StartOfMonth(FirstDate),
  // Today's date obtained via formula and converted to the last day of the month
 End_Date = Date.EndOfMonth(Date.From(DateTime.LocalNow())),
  // Create a list of dates by day between the start and end date
 Source = List.Dates(
        Start_Date,
        Duration.Days(End_Date-Start_Date)+1,
        #duration(1, 0, 0, 0)),
  // Convert the list to a table, setting column name and format to Date
  #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(),type table[Date=Date.Type], null, ExtraValues.Error),
  // Add year column
  #"Extract Year" = Table.AddColumn(#"Converted to Table", "Year", each Number.ToText(Date.Year([Date])), type text),
  // Add month number column
  #"Extract Month No." = Table.AddColumn(#"Extract Year", "Month No.", each Number.ToText(Date.Month([Date]),"D2"), type text),
  // Add month name column
  #"Extract Month Name" = Table.AddColumn(#"Extract Month No.", "Month", each Date.MonthName([Date]), type text),
  // Add month short name column
  #"Extract Month short name" = Table.AddColumn(#"Extract Month Name", "Month Short", each Text.Start([Month], 3), type text),
  // Add year-month number column
  #"Create YYYY-MM" = Table.AddColumn(#"Extract Month short name", "YYYY-MM", each Text.Combine({[Year], [#"Month No."]}, "-"), type text),
  // Add end of month column
  #"Calculate EoM date" = Table.AddColumn(#"Create YYYY-MM", "End Of Month", each Date.EndOfMonth([Date]), type date),
  // Add column that tests date to see if it is a weekend
  #"Is weekend" = Table.AddColumn(#"Calculate EoM date", "Weekend", each if Date.DayOfWeek([Date])=5 or Date.DayOfWeek([Date])=6 then 1 else 0, Int64.Type),
  // Add a column for eight standard hours per week day
  #"Calculate Standard hours" = Table.AddColumn(#"Is weekend", "Standard Hours", each if [Weekend] = 1 then 0 else 8, type number),
  // Add a column with short month- year
  #"Create MMM-YYYY" = Table.AddColumn(#"Calculate Standard hours", "MMM-YYYY", each Text.Combine({[Month Short], [Year]}, "-"), type text),
  // Sort rows
  #"Sorted Rows" = Table.Sort(#"Create MMM-YYYY", {{"Date", Order.Ascending}}),
  // Add an index column
  #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type)
in
    #"Added Index"

Output

This is what the Calendar table would look like.

Calendar table Calendar table