Microsoft Power BI Cookbook

Add date intelligence columns via SQL

1. Add two columns to the date table stored in the relational database:
   • A natural hierarchy string (2009-Jan) and an integer column, such as Calendar Year Month Number
2. Execute an UPDATE statement that populates the string column via the concatenation of the Year and Month columns.

3. Create a table with three columns, Year, Month Number, and an Identity column, with an increment value of 1:

Create Table dbo.TempTblYearMo( [Calendar Year] int not null, [Calendar Month Number] tinyint not null, [Calendar Yr Mo Index] smallint identity(1,1) not null)

4. Execute an Insert Into SQL statement to load this table:
   • Select, group, and order the Calendar Year and Calendar Month Number columns from the existing date ...