Create a Date table with M code

Posted by By Giulio Stefanica 3 Min Read 361 Views

One of the most important tables in any data model is the Date/Calendar table. Here I will show how quickly and easily this can be done inside Power Query by using M code.

Step 1: Create a Blank Query

I continue with the same report I created in the last tutorial where I showed how to import data and create a data model in Power BI Desktop which can be found here. Inside the Power Query Editor, I go to New Source and click on Blank Query.

Transform lose & 4ppIy Home New Source • DK Retailer - start- Power Query E&ltor go I distinct. go I unique Add Column Data source settings Data Sources View Manage Parameters • Parameters Tools Help Recent Sources • Enter Data Refresh Preview • Properties Advanced Editor Manage • Query Choose Remove Columns • Columns • Manage Columns Keep Remove Rows • Rows • Reduce Rows Split Column • Group Most Common Close Sort Data Type: Text • use First Row as Headers • Replace Values Transform Merge Queries • Append Queries • Combine Files Combine Text Analytics @ Vision Azure Machine Learning Al Insights Queries Excel Workbook SQL server Analysis Services Text,'csv Web OData feed Blank Query - Table. {f Query Settings PROPERTIES Name Customers All Properties APPLIED STEPS Source Navigation X Changed Type x "Customer ID", type text}, {"Customer Name" , type text}} ) • ABC Customer Name 100 % 4 ABC Customer ID • Valid • Error • Empty Clooo CIOOI C1002 Cloos C1004 • Valid • Error • Empty go I distinct. go I unique Ana bel Luet'/ Ailsun Padberrv Jolvn Sivier Rusty Brver Amie Seeviour Terri Hardey Rossie Gellert Maddie Gavaghan Sammie Cham Morissa Adamo Norina 'Wyeth Darline Souten Jazmin Scholfield Sigmund MacPake Thurstan Legon Dinny Trenear Sherve Heinrici Yettv Ericssen Janeczka Broxap Wiatt Bordman Jeanette Diament Morry Izkoveski Brea r Gedge Thelma Landrieu Lanny Noseworthy Gregoire Kalinovich Teodor Goodale Blondy Surphliss Caryl Orts Elvina MacGiIItvrav Arlen Riofi Barri Pentlow 100 % More... Blank Query Write a query from scratch. 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 C1008 cloog CIOIO CIOII C1012 C1013 C1014 C1015 C1016 C1017 C1018 c1019 C1020 C1021 C1022 C1023 C1024 C1025 C1026 C1027 C1028 c1029 C1030 C1031 COLUMNS, 801 ROWS Column profiling based on top IOCO rows PREVIEW DOWNLOADED AT 15:16

Step 2: Paste the M code

Then I click on the Advanced Editor button which can be found under the Home tab in the Query group. Inside the Advanced Editor there are already four lines of code. I start by selecting all and deleting them after which I will paste the following M code and click Done.

Advanced Editor Queryl I let fnDateTabIe = (StartOate vas date, EndDate as Odate, F Y Starthonth as number) vas table OayCount = , Duration. Oays(ouration. From(EndDate - IStartDate)), Source = a, TableFromList = Table. FromList(Source, VSpIitter . Split8yNothing()), ChangedType Table. , type Odate}}), RenamedCoIumns = Table . RenameCoIumns (ChangedType, {{"CoIumnI" , "Date"}}), = Table.AddCoIumn(RenamedCoIumns, each Date. text), "Year", = Table .AddCoIumn(RenamedCoIumns, "Year , Number", veach Date. = Table.AddCoIumn(InsertYear, "Quarter of Year", each = Table .AddCoIumn(InsertQuarter, "Month oof Year", , each , Date.Month(CDate]), type text) , Table .AddCoIumn(InsertMonth, "Day Month", veach = Table .AddCoIumn(InsertDay, "Datelnt", each [Year] * + løø—, [Dayofhonth]), Table.AddCoIumn(InsertOayInt, "Month Name", , each , "WV"") , type text), InsertMonthName = Table .AddCoIumn(InsertMonthName, "Month i In each (try(Text. 3)) otherwise [MonthName]) " " & , Number . ToText( [Year] = Table .AddCoIumn(InsertCaIendarmonth, "Quarter In , Calendar", , each "Q" Number " " & = Table.AddCoIumn(InsertCaIendarQtr, "Dab' In 0 Week", = Table.AddCoIumn (InsertDayWeek, " Day oof IQeek Name", "dddd"), type text), , each = Table.AddCoIumn(InsertDayName, "Week Ending", leach Oate.EndOfKeek(CDate]), otype date), Table.AddCoIumn(InsertKeekEnding, "Week Number", veach Date.WeekOfYear(CDate])), = Table .AddCoIumn(InsertWeekNumber, "Month and Year", loeea * Iløe), veach [Year] = Table.AddCoIumn (Insert,'40nthnYear, "Quarter and Year" , *010B) ' , each [Year) * 100% * [QuarterOfYear] ChangedTypeI = Table. TransformCoIumnTypes(InsertQuarternYear, {f "Quarter and Year", Number", Int". Type}, {"Year", , type text}, { "month,and OYear", Int = Table.AddCoIumn(ChangedTypeI, "Short Year", Text. 02), type text), AddFW= Table.AddCoIumn(InsertShortYear, "FY", veach velse [ShortYear])) AddFY i fnDateTabIe No syntax errors have been detected. Cancel

Step 3: Enter Parameters

The next step is to select a Start and End Date and a Financial Start Month. In the data sample I am working with historical sales, and I can quickly verify that the first purchase date was in January 2015 going all the way to January 2018. So as the Start Date I choose 1/1/2015 and for the End Date I will go all the way to 31/12/2019. For the financial start month, I will type 1 for January because for many Danish companies the fiscal year follows the calendar year.

Queries [6) Dimension Tables 151 Customers Locations Produ Cts Salespersons Query 7 Fact Tables [Il sales Other Queries InsertShortYear Table. angedTypeI, "Short Year" , each Text Enter Parameters Start Date 1/1/2015 End Date 31-12-2019 FYStartMonth function (startDate as date, EndDate as date, FYStartMonth as number) as table

Step 4: Rename the Query

As a default the new query will be placed in the Other Queries folder and named “Invoked Function“. I double click on it and rename it to “Dates” and move it into the Dimensions Tables folder group. Then click on Close and Apply.  

Step 5: Create Relationship with the Fact table

The last step is to create the relationship between the Dates table and the Fact table. For this, I navigate to the Model canvas and identify the columns that match. I drag the Date column from the Dates table to the Purchase Date column in the Fact Sales.

Now the Data Model is complete and ready to start writing DAX formulas and discover insights.