Forum Discussion
Convert year and ISO week number into date
Hello,
I have the following table and i am trying to create a relationship between this table and my calendar table. Is it possible to convert the combination of year and ISO week number into a date? If yes, how to proceed? Any help is much appreciated!
| Year | ISO Week number | Items | Returns | Defective |
| 2020 | 4 | 5 | 6 | 15 |
| 2021 | 7 | 3 | 3 | 13 |
| 2022 | 8 | 3 | 3 | 14 |
| 2022 | 8 | 2 | 7 | 17 |
| 2022 | 4 | 2 | 2 | 8 |
| 2021 | 3 | 6 | 9 | 18 |
| 2022 | 2 | 3 | 4 | 9 |
| 2021 | 10 | 3 | 7 | 20 |
| 2022 | 6 | 7 | 8 | 21 |
| 2022 | 3 | 2 | 5 | 10 |
| 2020 | 3 | 9 | 3 | 15 |
| 2022 | 7 | 6 | 3 | 16 |
Hi Anonymous ,
How about this:
I assumed you would like to get the first day of the respective week in date format.
I took inspiration from here.
Here the code for the custom column:
Date.From(Number.From(Date.AddDays(Date.FromText("1/1/"&Number.ToText([Year])),-3))-Date.DayOfWeek(Date.FromText("1/3/"&Number.ToText([Year])))-1+[ISO Week number]*7)Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
1 Reply
Hi Anonymous ,
How about this:
I assumed you would like to get the first day of the respective week in date format.
I took inspiration from here.
Here the code for the custom column:
Date.From(Number.From(Date.AddDays(Date.FromText("1/1/"&Number.ToText([Year])),-3))-Date.DayOfWeek(Date.FromText("1/3/"&Number.ToText([Year])))-1+[ISO Week number]*7)Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/