Blog Archives

Power Query: Week or Weak

So it all started rather innocuously creating a dynamic date dimension using a Power Query function based on posts by Chris Webb and Matt Mason. Then I got into weeks…

  1. Lets take a set of dates covering four weekends across a new year (2015-18-12 to 2016-01-11)
(StartDate, EndDateas table =>
let

StartDate #date(2015,12,18),

EndDate #date(2016,1,12),

DayCount Duration.Days(Duration.From(EndDate – StartDate)),

Source List.Dates(StartDate,DayCount,#duration(1,0,0,0)),

TableFromList Table.FromList(SourceSplitter.SplitByNothing()),

ChangedType Table.TransformColumnTypes(TableFromList,{{“Column1”type
date}}),

RenamedColumns Table.RenameColumns(ChangedType,{{“Column1”“Date”}})
in

RenamedColumns

  1. Add Day Name – Date.DayOfWeekName
  2. Add Day of Week – Date.DayOfWeek
  3. Add Week of Year – Date.WeekOfYear
  4. Add Start of Week – Date.StartOfWeek
  5. Add End of Week – Date.EndOfWeek
  6. Add Week of Month – Date.WeekOfMonth
  7. Add Is In Current Week – Date.IsInCurrentWeek with my local machine date set to January 1 2016

Giving you:

There are a few unanticipated results above:

  1. DayOfWeek shows Monday = 0. This contradicts the msdn documentation which states the firstday argument (which is optional and I didn’t use) defaults to Sunday.

    It is also different to the DAX function WEEKDAY() which returns an integer 1 to 7 (not 0 to 6) with a default of the week actually beginning on Sunday (on the same machine with same locale settings):

    WEEKDAY_DAX = WEEKDAY(‘Invoked Function (2)[Date])

    Similarly it is different from t-SQL DATEFIRST and ISO 8601 which have a base value of 1.

    This is being driven by locale settings in the pbix file, as below.

    File | Options and settings | Options | Current File | Regional Settings | Locale

    2017-01-26_16-17-32

    It looks like the Power Query function is aligned to C# enumeration but default behaviour is actually set by locale.

  2. WeekOfYear isn’t ISO 8610 compliant. This is just silly so you should vote for this Power BI idea.
  3. The following mismatch happens when a week spans January 1:
    Day of Week Week of Year Start of Week End of Week Week of Month Is In Current Week
    Day of Week

    N

    Y

    Y

    N

    Y

    Week of Year

    N

    N

    N

    Y

    N

    Start of Week

    Y

    N

    Y

    N

    Y

    End of Week

    Y

    N

    Y

    N

    Y

    Week of Month

    N

    Y

    N

    N

    N

    Is In Current Week

    Y

    N

    Y

    Y

    N

If you’re interested in dates in t-SQL and SSIS, have a look at my previous post SSIS vs t-sql datetime datatypes

To be fair to Microsoft, the concept of a week is a trivial construct that only loosely aligned to celestial movements that made more sense with it’s origins in the Babylonian Calendar than it does in the Gregorian Calendar.

Advertisements
%d bloggers like this: