Forum Discussion
Converting text year to date in Power Query
Hi, i am trying to convert column Academic year which has been stored as a text field to a date format. When i do this however it returns 13/07/1905. Any ideas how i can fix this so it shows e.g. 2020 but in date format?
adam_mac your image shows the 2021 is an integer, and converting an integer to date will give you the erroneous results you show - because Power Query counts days like Excel, and July 13, 1905 is the 2021'st day since Dec 31, 1899.
You need to convert it to actual text first, then convert to date by adding a step, not hitting "replace current."
4 Replies
adam_mac your image shows the 2021 is an integer, and converting an integer to date will give you the erroneous results you show - because Power Query counts days like Excel, and July 13, 1905 is the 2021'st day since Dec 31, 1899.
You need to convert it to actual text first, then convert to date by adding a step, not hitting "replace current."
Hi adam_mac
Just change the type to date in PQ and you should get a date 01/01/2021. Then in the DAX table you can set the format to show only the year (2001 (yyyy)) while keeping the date type. See it all at work in the attached file.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
