Forum Discussion

monojchakrab's avatar
monojchakrab
Resolver III
3 years ago
Solved

convert text string to date in power query

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

Need Help
  • ImkeF's avatar
    ImkeF
    3 years ago

    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.



2 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    3 years ago

    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's avatar
      monojchakrab
      Resolver III
      3 years ago

      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