convert text string to date in power query

[monojchakrab]

Hey Good people,

I am grappling with a queer problem right now...

I have received a bunch of excel sheets, monthwise, but the monthname is input as a text string. e.g. the month of April, in the excel sheet is entered as "Apr". I have tried to use Date.FromText, but that is returning an error and so is any attempt to change the type from text to Date thru' the UI.

Is there a workaround for this without having to go back to each of the excel sheets and cnage the format manually, before loading to PQ?

Any help appreciated as I have more than 60 worksheets to work thru' with this messy date format.

Thanks in advance

[ImkeF · Accepted solution]

Hi @monojchakrab ,
yes, the implementation is a bit clumsy, as you have to cater for the number of days digits. The following works for me. Add a column with this formula (assuming your date values sit in column "Date"):

Date.FromText([Date], [Format = try "d MMM yyyy" otherwise "dd MMM yyyy", Culture = "de-DE"])

Please also check the file enclosed.

[monojchakrab]

Thanks @ImkeF - tried this without luck. I am now trying to get the month from the file attribute which has a date created column and extract the month from there.

But appreciate the leg-up