Search code examples
excelpowerquerym

Get date from weeknumber, dayofweek and year PowerQuery M

I want to create an extra column to my dataset. This needs to be done by using M in PowerQuery. I have the weeknumber, dayoftheweek and year available.

This can be done by using the following formula in Excel. I need something similar but then in M.

Date = DATE([year],1,-2)-WEEKDAY(DATE([year],1,3))+[week]*7 + [day]-1

For example: Week 2, Day 3, Year 2019 should be: 9-1-2019 (In #dd-mm-yyyy#)

Thanks in advance

Solution

  • This will do it

    = Table.AddColumn(Source, "Custom", each Date.From(Number.From(Date.AddDays(Date.FromText("1/1/"&Number.ToText([year])),-3))-Date.DayOfWeek(Date.FromText("1/3/"&Number.ToText([year])))-1+[week]*7+[day]-1) )