DateInt and ISOWeekNumber in Date Table

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.