Search code examples
powerquerym

DateKey formatted as YYYYMMDD

I have the following calendar table script:

let
    StartDate = #date(2016, 1, 1),
    EndDate = #date(2018, 12, 31),
    CurrentDate = DateTime.Date(DateTime.FixedLocalNow()),
    FiscalYearEndMonth = 6,
    ListDates = List.Dates(StartDate, Number.From(EndDate - StartDate)+1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(ListDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns as Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type to Date" = Table.TransformColumnTypes(#"Renamed Columns as Date",{{"Date", type date}}),
    #"Added Calendar MonthNum" = Table.AddColumn(#"Changed Type to Date", "MonthNum", each Date.Month([Date]), Int64.Type),
    #"Added Calendar Year" = Table.AddColumn(#"Added Calendar MonthNum", "Year", each Date.Year([Date]), Int64.Type),
    #"Added MonthYearNum" = Table.AddColumn(#"Added Calendar Year", "MonthYearNum", each [Year]*100 + [MonthNum] /*e.g. Sep-2016 would become 201609*/, Int64.Type),
    #"Added Day Num" = Table.AddColumn(#"Added MonthYearNum", "DayNum", each Date.Day([Date])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Datekey",{{"DayNum", Int64.Type}}),    
    #"Added Datekey" = Table.AddColumn(#"Added Day Num", "DateKey", each [Year]*100 + [MonthNum] + [DayNum]),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type1",{{"DayNum", Int64.Type}, {"DateKey", Int64.Type}})
in
    #"Changed Type"

It results in the following:

enter image description here

So the last column is causing me the headache - I think the code for that column is:

each [Year]*100 + [MonthNum] + [DayNum]

It seems like the first two elements of this expression get concatenated as expected but then DayNum is added e.g. for 1st of Jan we get 2016 concatenated with 1 to give 201601 but then the DayNum gets added mathematically to it, to give 201602 but I want it to give 20160101 (format YYYYMMDD)

Why is this happening and what is the correct M/PowerQuery to avoid this behavior?

Solution

  • I believe you are correct as far as what it's doing. How about this instead?

    each [Year]*10000 + [MonthNum]*100 + [DayNum]

    or you could do

    each [MonthYearNum]*100 + [DayNum]