BLOGS

Date Table in Power BI – M Language

Power BI Desktop supports Power Query Language which is nothing but M language for data modeling.

This article explains how we can create date table with all the necessary columns. Generating date table is easy in Power BI as we have different methods.

Refer my below article to generate date table manually or with single DAX query.

Date table in Power BI

Use the below M query to create a date table,

//Create Date Dimension

(StartDate as date, EndDate as date)=>

let

    //Capture the date range from the parameters

    StartDate = #date(Date.Year(StartDate), Date.Month(StartDate),

    Date.Day(StartDate)),

    EndDate = #date(Date.Year(EndDate), Date.Month(EndDate),

    Date.Day(EndDate)),

//Get the number of dates that will be required for the table

    GetDateCount = Duration.Days(EndDate – StartDate),

//Take the count of dates and turn it into a list of dates

    GetDateList = List.Dates(StartDate, GetDateCount,

    #duration(1,0,0,0)),

//Convert the list into a table

    DateListToTable = Table.FromList(GetDateList,

    Splitter.SplitByNothing(), {“Date”}, null, ExtraValues.Error),

//Create various date attributes from the date column

    //Add Year Column

    YearNumber = Table.AddColumn(DateListToTable, “Year”,

    each Date.Year([Date])),

//Add Quarter Column

    QuarterNumber = Table.AddColumn(YearNumber , “Quarter”,

    each “Q” & Number.ToText(Date.QuarterOfYear([Date]))),

//Add Week Number Column

    WeekNumber= Table.AddColumn(QuarterNumber , “Week Number”,

    each Date.WeekOfYear([Date])),

//Add Month Number Column

    MonthNumber = Table.AddColumn(WeekNumber, “Month Number”,

    each Date.Month([Date])),

//Add Month Name Column

    MonthName = Table.AddColumn(MonthNumber , “Month”,

    each Date.ToText([Date],”MMMM”)),

//Add Day of Week Column

    DayOfWeek = Table.AddColumn(MonthName , “Day of Week”,

    each Date.ToText([Date],”dddd”))

in

    DayOfWeek

 

It asks for start and end date. Once entered the date then click Invoke, it will generate the query.