DateInt and ISOWeekNumber in Date Table

[kcantor]

Creating a custom date table question.

I have been working with M to create a more responsive date table but I have found my knowledge to be lacking in the area requred to create certain fields. DateInt and ISOWeekNumber would be two of those fields. I think I can handle the other fields but I am going to provide all of the details below in case someone can help me clean it up as well.

My current query is this:

let
Source = List.Dates,
#"Invoked FunctionSource" = Source(#date(2014, 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow()) - #date(2014,1,1)), #duration(1, 0, 0, 0)),
#"Table from List" = Table.FromList(#"Invoked FunctionSource", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Index" = Table.AddIndexColumn(#"Table from List", "Index", 1, 1),
#"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Column1", "Date"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Month Number", each Date.Month([Date])),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Day", each Date.Day([Date])),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Day Name", each Date.ToText([Date],"ddd")),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Month Name", each Date.ToText([Date],"MMM")),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom4",{"Date", "Index", "Year", "Month Number", "Month Name", "Day", "Day Name"}),
#"Added Custom5" = Table.AddColumn(#"Reordered Columns", "Quarter Number", each Date.QuarterOfYear([Date])),
#"Duplicated Column" = Table.DuplicateColumn(#"Added Custom5", "Year", "Copy of Year"),
#"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"Copy of Year", "Short Year"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Short Year", type text}}),
#"Split Column by Position" = Table.SplitColumn(#"Changed Type","Short Year",Splitter.SplitTextByRepeatedLengths(2),{"Short Year.1", "Short Year.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Short Year.1", Int64.Type}, {"Short Year.2", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Short Year.1"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"Short Year.2", "Short Year"}}),
#"Added Custom6" = Table.AddColumn(#"Renamed Columns2", "Quarter Year", each Number.ToText([Short Year]) & "Q" & Number.ToText([Quarter Number],"00")),
#"Reordered Columns1" = Table.ReorderColumns(#"Added Custom6",{"Index", "Date", "Day", "Day Name", "Month Number", "Month Name", "Quarter Number", "Quarter Year", "Short Year", "Year"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns1",{{"Date", type date}, {"Day", Int64.Type}, {"Index", Int64.Type}, {"Month Number", Int64.Type}, {"Quarter Number", Int64.Type}, {"Month Name", type text}, {"Quarter Year", type text}, {"Year", Int64.Type}}),
#"Renamed Columns3" = Table.RenameColumns(#"Changed Type2",{{"Day", "DayofMonth"}}),
#"Inserted Day of Year" = Table.AddColumn(#"Renamed Columns3", "DayOfYear", each Date.DayOfYear([Date]), type number),
#"Sorted Rows" = Table.Sort(#"Inserted Day of Year",{{"Date", Order.Descending}}),
#"Renamed Columns4" = Table.RenameColumns(#"Sorted Rows",{{"Date", "DateKey"}}),
#"Duplicated Column1" = Table.DuplicateColumn(#"Renamed Columns4", "Year", "Year - Copy"),
#"Renamed Columns5" = Table.RenameColumns(#"Duplicated Column1",{{"Year - Copy", "YearKey"}, {"Month Number", "MonthOfYear"}, {"Quarter Number", "QuarterOfYear"}}),
#"Inserted Week of Year" = Table.AddColumn(#"Renamed Columns5", "WeekOfYear", each Date.WeekOfYear([DateKey]), type number),
#"Renamed Columns6" = Table.RenameColumns(#"Inserted Week of Year",{{"WeekOfYear", "WeekNumber"}})
in
#"Renamed Columns6"

What I need is this:

DateKey	DateInt	YearKey	HalfYearKey	QuarterKey	MonthKey	MonthOfYear	QuarterOfYear	DayOfYear	DayOfMonth	DayOfWeekMon	DayOfWeekSun	Month	Year	WeekNumber	WeekOfYearISO
8/31/2016	20160831	2016	20162	20163	20168	8	3	244	31	3	4	8	2016	36	35
8/30/2016	20160830	2016	20162	20163	20168	8	3	243	30	2	3	8	2016	36	35
8/29/2016	20160829	2016	20162	20163	20168	8	3	242	29	1	2	8	2016	36	35

Can anyone on here help me reconcile the two?

[KGrice · Accepted solution]

Hi @kcantor. I tried recreating your output. For a couple columns, I'm not sure if you wanted them as text and a duplicate as a number (you've got Year and YearKey that are the same "value" and the same for Month and MonthKey), so I only included one of each. These could be added back in easily.

I started with just the DateKey field, then added anything that could be done without custom columns first. The first thing that required any modification in the formula bar was DayOfWeekMon, where I added the option to make it work for Monday after using the standard UI DayOfWeek function. However, I also changed the names of the columns in the formula bar as I added them, just to save the renaming step(s).

Everything else but the WeekOfYearISO was combining text versions of the previous steps. If those are intended to be numbers, you could wrap the whole formula in Number.From.

For WeekOfYearISO, I left that to DAX. It's so much simpler right now, I didn't know if it was worth messing with in M. Here's an article outlining how to do it, but as you'll see, it's just one simple formula in DAX compared to lines of M code, though that might be worth it in the end if you don't have to add a DAX column to your paste-in calendar table every time. Here's the DAX as an added column:

WeekOfYearISO = WEEKNUM('Calendar'[DateKey], 21)

The 21 option isn't documented in DAX, but it works just the same as in Excel. If you'd like for the ISO functionality to come to M, I'd suggest voting on the community idea.

Here's the M code I used to get everything else:

let
    // Date Parameters
    startDate = Number.From(Date.From("1/1/2016")),
    endDate = Number.From(Date.From("12/31/2016")),

    Source = {startDate..endDate},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "DateKey"}}),
    #"Inserted Month" = Table.AddColumn(#"Renamed Columns", "MonthOfYear", each Date.Month([DateKey]), type number),
    #"Inserted Day" = Table.AddColumn(#"Inserted Month", "DayOfMonth", each Date.Day([DateKey]), type number),
    #"Inserted Year" = Table.AddColumn(#"Inserted Day", "Year", each Date.Year([DateKey]), type number),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Year", "QuarterOfYear", each Date.QuarterOfYear([DateKey]), type number),
    #"Inserted Day of Year" = Table.AddColumn(#"Inserted Quarter", "DayOfYear", each Date.DayOfYear([DateKey]), type number),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day of Year", "DayOfWeekSun", each Date.DayOfWeek([DateKey], 0), type number),
    #"Inserted Day of Week1" = Table.AddColumn(#"Inserted Day of Week", "DayOfWeekMon", each Date.DayOfWeek([DateKey], 1), type number),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Day of Week1", "WeekOfYear", each Date.WeekOfYear([DateKey]), type number),
    #"Added Custom" = Table.AddColumn(#"Inserted Week of Year", "DateInt", each Text.From([Year]) & Text.PadStart(Text.From([MonthOfYear]), 2, "0") & Text.PadStart(Text.From([DayOfMonth]), 2, "0")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "HalfYearKey", each Text.From([Year]) & Text.From(if [QuarterOfYear] <= 2 then 1 else 2)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "QuarterKey", each Text.From([Year]) & Text.From([QuarterOfYear])),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "MonthKey", each Text.From([Year]) & Text.From([MonthOfYear])),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom3",{"DateKey", "DateInt", "Year", "HalfYearKey", "QuarterKey", "MonthKey", "MonthOfYear", "QuarterOfYear", "DayOfYear", "DayOfMonth", "DayOfWeekMon", "DayOfWeekSun", "WeekOfYear"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"DateKey", Order.Descending}})
in
    #"Sorted Rows"

It's a different approach to creating the table from scratch, but I find it easier. Picked that up from Ken Puls' and Miguel Escobar's book, M is for (DATA) MONKEY.

[Jmenas]

Hi @KGrice,

thanks for the code is really easy to create the table with this code. I just have one question, do you know how to set the beggining od the week to Monday?  I try the DateOftheWeek function but didn't work.

Thanks in advance.

J