Hi,
I have a field , “Confirmed delivery date”, in format YYWWD (Year , week and day of week), which I want to convert into a date field. I.e 20145 should be converted into todays date 2020-04-03. Most preferably I want to do it in Power Query since I want to do other calculations based on the date field
Thanks in advance
/robert
Hi @robsmi,
I’ve split the Confirmed delivery date up in Year, Weeknumber and Weekday number
added a Calendar and Merged the two tables to return the date.
Here’s my sample file. I hope this is helpful.
eDNA - Convert YYWWD into date.pbix (165.4 KB)
1 Like
Thanks @Melissa , this expands my mind. Just an additional query here, how do you use query editor to add the Year & Week number & Weekday number columns. By writing M language or just hit the relevant buttons of query editor? thanks!
1 Like
Hi @luisa1989,
Looking back I did kinda leave the shortest response ever… 
I’ll make it up here.
Generally speaking I’ve made a habbit of using the Power Query interface as much as possible and only making minor changes to the M code generated by the UI - when I need to.
I removed the auto generated Changed Type, step. Now to extract the Year we need the first 2 characters from the Confirmed delivery date column.
The UI created this M code:
Table.AddColumn(Source, “First Characters”, each Text.Start([Confirmed delivery date], 2), type text)
To turn it into a proper Year - we have to make some changes to the M code inside the formula bar:
.
To extract the Weeknumber, select the first column and choose Extract, Range
Power Query is zero based so starting position 2 will get us the third character and the number of characters to extract is 2
The UI created this M code:
Table.AddColumn(#“Inserted First Characters”, “Text Range”, each Text.Middle([Confirmed delivery date], 2, 2), type text)
That’s almost perfect! We just need to change the type to Number
.
To extract the weekday number
The UI created this M code:
Table.AddColumn(#“Inserted Text Range”, “Last Characters”, each Text.End([Confirmed delivery date], 1), type text)
And again only changing the type from Text to Number is enough…
.
Perform a 3 way lookup by Merging our Query with the Dates Table (hold down CTRL to multi select)
Only expand the Date column
And finally clean up the helper columns using Choose columns
I hope this was helpful.
6 Likes
It’s great to know that you are making great progress with your query @luisa1989. Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!
Than you Melissa for a brilliant support. Already your first answer was good-enough, your detailed instructions was brilliant. Everything is crystal clear, no confusion left