Create a PowerPivot Date Table with Power Query - Excelerator BI

Create a PowerPivot Date Table with Power Query

I really love PowerPivot and Power Query. I have been doing Rob Collie’s on line training course on using PowerPivot. When I got to the section on Time Intelligence, it occurred to me that Power Query could be used to create the date table that you need. I did a bit of research and pieced together some techniques I found.

Here is the code to create a Date table for PowerPivot with Power Query.  Of course you can modify this to add or remove different columns if you like.

let
     Source = List.Dates,
     InvokedSource = Source(#date(2010, 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow())-#date(2010,1,1)), #duration(1, 0, 0, 0)),
     TableFromList = Table.FromList(InvokedSource, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
     RenamedColumns = Table.RenameColumns(TableFromList,{{"Column1", "Date"}}),
     InsertedCustom = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),
     InsertedCustom1 = Table.AddColumn(InsertedCustom, "ShortYear", each Date.Year([Date])),
     InsertedCustom2 = Table.AddColumn(InsertedCustom1, "MonthNum", each Date.Month([Date])),
     InsertedCustom3 = Table.AddColumn(InsertedCustom2, "Day", each Date.Day([Date])),
     InsertedCustom8 = Table.TransformColumnTypes(InsertedCustom3,{{"Date", type date}}),
     InsertedCustom4 = Table.AddColumn(InsertedCustom8, "DayName", each Date.ToText([Date],"ddd")),
     InsertedCustom5 = Table.AddColumn(InsertedCustom4, "MonthName", each Date.ToText([Date],"MMM")),
     InsertedCustom6 = Table.AddColumn(InsertedCustom5, "QuarterNumber", each Date.QuarterOfYear([Date])),
     ChangedType = Table.TransformColumnTypes(InsertedCustom6,{{"ShortYear", type text}}),
     SplitColumnPos = Table.SplitColumn(ChangedType,"ShortYear",Splitter.SplitTextByRepeatedLengths(2),{"ShortYear.1", "ShortYear.2"}),
     ChangedType1 = Table.TransformColumnTypes(SplitColumnPos,{{"ShortYear.1", type number}, {"ShortYear.2", type number}}),
     RemovedColumns = Table.RemoveColumns(ChangedType1,{"ShortYear.1"}),
     RenamedColumns1 = Table.RenameColumns(RemovedColumns,{{"ShortYear.2", "ShortYear"}}),
     InsertedCustom7 = Table.AddColumn(RenamedColumns1, "Quarter", each "Q"&Number.ToText([QuarterNumber])&"/"&Number.ToText([ShortYear])),
     RemovedColumns1 = Table.RemoveColumns(InsertedCustom7,{"ShortYear", "QuarterNumber"})
in
     RemovedColumns1

One line of code above is the “secret sauce” – see below. This line of code was created in the advanced editor, and it creates a list of every day starting from 1 Jan 2010 through to today’s date. You can of course change the start date to suit your needs.

InvokedSource = Source(#date(2010, 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow())-#date(2010,1,1)), #duration(1, 0, 0, 0)),

 

If you want a comprehensive lesson on how to use Power Query, checkout my training course here https://exceleratorbi.com.au/power-query-online-training/

4 thoughts on “Create a PowerPivot Date Table with Power Query”

  1. Matt, whats the trick to add the fiscal year for June base using the 184 day trick in the duration you mentioned earlier…I can’t seem to find where I wrote this down when you showed us in Perth! Cheers again!

    1. ok sorry found the newer article for those interested.

      Table.AddColumn(Custom1, “Fin Year”, each Date.Year([Date]+#duration(184,0,0,0)), Int64.Type)

  2. Matt,

    I am new to power pivot. I attended the CPA seminar you did last week. I have copied this into my power query, but It’s not doing anything.

    I click close and load and it creates one row with the text from the code above. What and I doing wrong?

    Thanks

    Matt

    1. It is very hard to say without looking. Do you have the latest version of Power Query? Are you pasting this code into the Advanced Editor?

      1. Open new workbook.
      2. Goto Power QueryFrom Other SourcesBlank Query
      3. ViewAdvanced Editor
      4. Replace all the code with the code from my website.

      Is this what you did?

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top