• A cleanshaven middle-aged man wearing glasses and a dark green sweater
  • 0:42 am

Power BI M Code Date Table

For Power BI, if your data source does not already have a date dimension table, Microsoft strongly recommends adding one in Power Query M-code. Here we show how to do that for a fiscal year date table.

Add a blank query and use the ‘Advanced Editor’ to enter the following code:

let

// Generate a list of continuous dates for ~10 years (from 2020/04/01)
Source = List.Dates(#date(2020,04,01), 365*10, #duration(1,0,0,0)),

// Convert the list of dates to a table
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

// Name the first column 'Date'
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),

// Duplicate the column and call it 'Year'
#"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "Date", "Year"),

/* Convert the 'Year' column into a fiscal year (FYXXXX) for an April to
    March fiscal year
  */
#"Extracted Year" = Table.TransformColumns(#"Duplicated Column",{{"Year", each "FY" & Text.From(if Date.Month(_) > 3 then Date.Year(_) + 1 else Date.Year(_)), Text.Type}}),

// Duplicate the 'Date' column and call it 'Quarter'
#"New Column1" = Table.DuplicateColumn(#"Extracted Year", "Date", "Quarter"),

/* Convert the 'Quarter' to be a column for the fiscal quarter
    (FYXXXXQZ) for an April to March fiscal year
  */
#"Calculated Quarter" = Table.TransformColumns(#"New Column1",{{"Quarter", each "FY" & Text.From(if Date.Month(_) > 3 then Date.Year(_) + 1 else Date.Year(_)) & "Q" & Text.From(if Date.Month(_) < 4 then 4 else Date.QuarterOfYear(_) - 1), Text.Type}}),

// Duplicate the 'Date' column and call it 'Month'
#"New Column2" = Table.DuplicateColumn(#"Calculated Quarter", "Date", "Month"),

/* Convert to the 'Month' calendar to the month's name */
#"Extracted Month Name" = Table.TransformColumns(#"New Column2", {{"Month", each Date.MonthName(_), type text}}),

// Duplicate the 'Date' column and call it 'MonthKey'
#"New Column3" = Table.DuplicateColumn(#"Extracted Month Name", "Date", "MonthKey"),

/* Convert 'MonthKey' to a column containing 'Mn' where 'n' is the month
    number.
  */
#"Extracted Month" = Table.TransformColumns(#"New Column3",{{"MonthKey", each "M" & Text.From(Date.Month(_)), Text.Type}}),

// Convert the 'Date' column to be of type 'datetime'
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Month",{{"Date", type datetime}})

in

#"Changed Type"

After that, follow Microsoft’s guidance on using date tables in Power BI. You will want to create date hierarchies for each date table you create (so that you can drill into fiscal year, fiscal quarter, month, etc). The documentation for creating date hierarchies is unfortunately buried somewhere in a video on Microsoft Learn in a Data Analyst certification learning path, so I may create a brief tutorial on this site, at some point.

Inspired by Generating a date dimension table in Power Query by Chris Webb.

See Also