Date format wrong in excel source

[anoomohan]

hi,

My excel source has multiple columns with 3 columns for date - YEAR, MONTH NAME, DAY NUMBER  - but issue is all these 3 columns are formatted in text (year and month) and day number in numeric value ; so when i use this in PBI desktop, i am unable to convert these fields to date format and getting error. 

e.g., if i change year column to date, for year 2023, i am getting 15-07-1905 - PLEASE ADVISE HOW TO solve this?

  after changing year column to date  - getting below

   

[anoomohan]

It works, thank you

[DataNinja777 · Accepted solution]

Hi @anoomohan 

I am also not that familiar with M language, but the good thing about Power Query is that you can just click ribbons to achive the tasks which are not that complicated.  For this task, I just clicked the ribbons mostly to produce the required output.  

I attach the pbix file in the link below for this task in which you can see the applied steps. 

https://1drv.ms/u/s!AlqFfzVTqicpnWvUaoX8e76td6aH?e=KUiM3V