Converting text year to date in Power Query

[adam_mac]

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? 

[edhans]

Glad to help @adam_mac - one of those live and learn things on quirks of Power Query. 😁

[edhans · Accepted solution]

@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."

[adam_mac]

hi @edhans , that worked great. Thank you very much! i have been stuck on that for embarassingly long! 

[AlB]

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