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/
By changing the second line of coding, we can control the beginning and ending date of the table. I wanted the table to end on 30/06/2020, I have to input #date(2020,7,1).
Original
InvokedSource = Source(#date(2010, 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow())-#date(2010,1,1)), #duration(1, 0, 0, 0)),
Updated
InvokedSource = Source(#date(2018, 7, 1), Duration.Days(#date(2020,7,1)-#date(2018,7,1)), #duration(1, 0, 0, 0)),
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!
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)
This could be another alternative
= Table.AddColumn(RemovedColumns1, “Fiscal Year”, each if [MonthNum]>=7 then [Year]+1 else [Year])
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
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?