ISO week number option on Date.WeekOfYear function
It would be really convenient to align the M function Date.WeekOfYear to the Excel WEEKNUM function to return a ISO 8601 compliant week number. Time intelligence properties are the starting point for a plethora of other calculations. And this "tile" is missing.
You can use a calendar table linked to your Date column
It just cost me quite some time to see that the M-function in Query-Editor, which is generated by
Add Column - Date - Week - Week of Year
really behanved different from the function "WEEKNUM".
It really is solved quite satisfactory in Excel.
It should REALLY by introduced into M, so that I can append tables with WEEK-comlumns efficiently in query designer.
Luc van de Plas commented
That doesn't seem to fix the problem:
Expression error: The name 'weeknum' wasn't recognized. Make sure it's spelled correctly.
input given: =weeknum(date,21)
Maksym Koronenko commented
Dear Jeffrey, unfortunatelly DAX function like M functions both returns incorrect week number, it's not ISO number
Roberto Campassi commented
Thank you very much for your suggestion, Jeffrey.
However, it would be much better to be able to reference a Power Query table cell for other interim evaluations and calculations... before loading all the needed structures in DAX and without writing kilometers of code only to obtain this result.
Jeffrey Wang commented
If all you need is to add a column to a table, one option today is to add a calculated column using DAX WEEKNUM function that behaves identically to Excel's.