Date Table in Power BI – M Language

ByHariharan Rajendran

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.

About the Author

Hariharan Rajendran author

Hariharan Rajendran is a Microsoft Certified Trainer and Lead BI Solutions Consultant with 8+ years of experience in Database, BI and Azure platforms. Hariharan is also an active community leader, speaker & organizer and leads the Microsoft PUG (Power BI User Group – Chennai), SQLPASS Power BI Local Group – Chennai and an active speaker in SQL Server Chennai User Group and also a leader in Data Awareness Program worldwide events. Hariharan also frequently blogs (www.dataap.org/blog), provides virtual training (on ad-hoc basis) on Microsoft Azure, Database Administration, Power BI and database development to worldwide clients/audience.

Leave a Reply