Power Query Date Column

[leimst]

Hello,

I have an Excel spreadsheet that I am pulling into Power Query and manipulating. One of the columns is a date field in the format "Mmm-yy". When I try to change the format of that column to "Date" in Power Query, it converts Oct-14 (which represents Oct. 2014) to 10/14/15, thus changing the year. Can anyone tell me how to use Power Query to convert that to the correct month and year?

Thank you in advance for any help,

leimst

 

[RoryA]

What does the actual cell in the source worksheet contain as a value? The text "Oct-14" or a true date value?

 

[leimst]

It is text...actually has the " ' " tick mark in front of it, so " 'Oct-14 ".

 

[RoryA]

Can you change the source to use say 1 Oct 14 and just format it as mmm-yy? That would fix the PQ issue.

 

[leimst]

I could and actually did do that but wanted to figure out how to solve the issue in PQ...I have heard so much about how amazing it is and what all you can change and manipulate. I wanted to set up a PQ query so that when I bring new data in each month PQ automatically performs the ETL on it and brings it in. In fact, I have it set up to do that...all I have to do is paste the new files into the source folder that I set up but I can't seem to figure out this piece of it.

 

[RoryA]

You could add a new calculated column using a formula like:
=Date.FromText("01-"&[Date field]))