Search code examples
datepowerbiformulapowerqueryisodate

Get a date from ISO week and year in power query

I have a column with a year and a ISO week. I would like to get the corresponding date, but at the moment my formula is wrong.

I have the following table:

Year  Week 
2020   52
2020   53
2021    1
2021    2

I used in power query editor the following formula:

Date.StartOfWeek(Date.AddWeeks(#date([Year], 1, 1), [Week]), Day.Monday)

and I obtained:

Year  Week  Date
2020   52   28.12.2020
2020   53   04.01.2021
2021    1   04.01.2021
2021    2   11.01.2021

What I would like to have instead:

Year  Week  Date
2020   52   21.12.2020
2020   53   28.12.2020
2020    1   04.01.2021
2021    2   11.01.2021

For example, in DAX, this works:

Date = DATE([Year],1,-2)-WEEKDAY(DATE([Year],1,3))+[Week]*7

But I would prefer to have it in power query because my data source needs to be updated regularly. Thank you for your attention!

Solution

  • In case you have the same problem, this works now:

    Date.AddDays((Date.AddDays(#date([Year],1,1),-4)),(-Date.DayOfWeek(Date.AddDays(#date([Year],1,1),-4)) + [Week]*7))