Forum Discussion

im_92's avatar
im_92
Frequent Visitor
5 years ago
Solved

Convert text to date

Hi all, 

 

I am trying to convert a text column to a date format. 

The original column contains the date in this format: wednesday 2 october 2018. 
I want to have a column with the date in this format: 02-10-2018

 

Any suggestions? 

thanks 

 

  • jthomson's avatar
    jthomson
    5 years ago

    If you split the column by the first instance of a space, i.e. getting rid of the day of the week, does that get it into a close enough format that it can automatically recognise it's a date?

4 Replies

  • jthomson's avatar
    jthomson
    Solution Sage
    5 years ago

    If you split the column by the first instance of a space, i.e. getting rid of the day of the week, does that get it into a close enough format that it can automatically recognise it's a date?

    • Daviejoe's avatar
      Daviejoe
      Memorable Member
      5 years ago

      im_92   The explanation that jthomson provided is the simpler way of what I posted.

       

      Use a text delimiter of a space to remove the day of week and then you can format as a date.  

       

      Job done.

  • Daviejoe's avatar
    Daviejoe
    Memorable Member
    5 years ago

    This should work, the bold text can be added after your source code

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKk9NyUstTkmsVDBSyE8uyU9KLVIwMjC0UIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}}),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Changed Type", "Text After Delimiter", each Text.AfterDelimiter([Date], " "), type text),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Text After Delimiter",{{"Text After Delimiter", type date}})
    in
    #"Changed Type1"