1

I am generating a Calendar table which can then be used to create a Gantt Chart visual on Power BI.

I have generated a list of dates in Power Query. Now I need the week number and year values for each of these dates.

The M query has a function

Date.WeekOfYear

, but this does not return the ISO Week number.

each Date.WeekOfYear([DateColumn], Day.Monday)

On the other hand, DAX has a function

WEEKNUM

which gives the ISO Week number.

ISOWeekNumber = WEEKNUM('Calendar'[DateColumn], 21)

I understood from looking up on the internet that DAX is more measures and calculated columns.

So, in this case, should I use Power Query or DAX if I want Year, Week Number, Week of the Day Number, etc.? What is the right thing to do and what are the implications of choosing one over another?

The algorithm to generate ISO week number looks complicated in Power Query and there seems to be no ready function to generate ISO week number.

Improve this question
5
  • 1
    It is recommended that make your transformations (ETL) as close to source as possible! This is motto! however, Regarding Date tables, I always do it using DAX! Both of them is OK! Pick the one who does the job best!
    – Ozan Sen
    Commented Nov 19, 2022 at 9:20
  • 1
    "Data should be transformed as far upstream as possible, and as far downstream as necessary." - ssbipolar.com/2021/05/31/roches-maxim A DAX calculated table is calculated at refresh time, and has similar performance to a table loaded with Power Query.
    – David Browne - Microsoft
    Commented Nov 19, 2022 at 15:01
  • 1
    @DavidBrowne-Microsoft Are you sure about performance comparison between dax calc. table and PQ Table? I mean did you test it with performance result?
    – Ozan Sen
    Commented Nov 19, 2022 at 18:44
  • 2
    Yes. Processing time might be more or less depending on the queries. But once processed a DAX calculated table is identical to a table read from Power Query. Read: sqlbi.com/articles/…
    – David Browne - Microsoft
    Commented Nov 19, 2022 at 19:40
  • Thank you for the article! I learned what I needed to learn 😊👍
    – Ozan Sen
    Commented Nov 20, 2022 at 13:48

1 Answer 1

Reset to default
3

Whether to generate this in DAX or PQ probably depends on what you are going to be doing with the results.

But it's not that difficult to generate it in Power query

Here is one function to generate ISO Weeknumber in Power Query (adapted from an Excel VBA function I used from before that function was added to the Excel library, originally developed, I believe, by Daniel Mahar).

//fnISOWeekNum

(theDate as date) =>
let
   a = Date.AddDays(theDate,-1),
   b = Date.DayOfWeek(a,Day.Sunday),
   c = Date.AddDays(theDate,-b + 3),
   d2 = #date(Date.Year(c),1,3),
   IWN  =  Number.IntegerDivide(Number.From(theDate)-Number.From(d2) + Date.DayOfWeek(d2,Day.Sunday)+6,7)

in
    IWN
Improve this answer
1
  • Very Good answer Indeed!
    – Ozan Sen
    Commented Nov 20, 2022 at 13:50

Not the answer you're looking for? Browse other questions tagged or ask your own question.