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

About Karl Beran

I take interest and satisfaction from enabling people within an organisation to have an accurate, concise and flexible representation of the right information they need, when (or before) they need it. I believe this allows the business to make both operational and strategic decisions quickly and with more confidence, thus improving the agility and efficiency of the company. Having worked with BI stacks from the major vendors, the focus of my blog is on BI principals and strategy with examples from the Microsoft stack as I believe it has the most dynamic and engaged user community with the knowledge base and components fairly readily available to new developers.

Posted on January 19, 2017, in Power BI and tagged , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: