Extract Date From Text String in Power Query

1. Hi Rick, Great blog!
   I get an error message which drives me crazy.
   I read a csv file into PQ, the file contains a header and 1 data record.
   CSV data:
   ts_log,otFtRoomSetpoint,otFtRoomTemperature (ts_log is a date-time object if type string)
   03062024 00:00, 21.5,21.5
   PQ:
   Adding a custom column
   = Table.AddColumn(#”Removed Other Columns”, “Date”, each Date.FromText( [ts_log], [Format=”ddMMyyyy hh:mm”]))

   I constantly get this error message:
   “Expression.Error: We cannot convert a value of type Record to type Text.
   Details:
   Value=
   Format=ddMMyyyy hh:mm
   Type=[Type]”

   Adding Culture=”nl-NL” or using Format=”ddMMyyyy” did not solve the problem. Any idea what this error message may cause?
   Thanks

   Reply

   • Hey Fred. There are two issues here:

     1. Your text value, besides containing the datetime value, also contains ” 21.5, 21.5″. The first step would be to make sure your text value only includes the datetime component. One way to do that is by using:

     Text.BeforeDelimiter( "03062024 00:00, 21.5,21.5", ",")

     2. The second issue is that you used the Date.FromText function on a datetime value. You can instead use

     let 
       myText = Text.BeforeDelimiter("03062024 00:00, 21.5,21.5", ","),
       DateTimeFromText = DateTime.FromText( myText , [Format= "ddMMyyyy hh:mm"] )
     in 
       DateTimeFromText 

     Alternatively, you could also extract only the date value as text, and then use the Date.FromText function as follows:

     let 
       myText = Text.BeforeDelimiter("03062024 00:00, 21.5,21.5", " "),
       DateFromText = Date.FromText( myText , [Format= "ddMMyyyy"] )
     in 
       DateFromText 

     Hope that helps!

     Reply

     • Thanks Rick for your detailed answer.

       But what I do not understand: why does the statement “= Table.AddColumn(#”Removed Other Columns”, “Date”, each Date.FromText( [ts_log], [Format=”ddMMyyyy hh:mm”]))” , especialy “Date.FromText( [ts_log]” , does not yield the text of column [ts_log] and put the result in a new column.

       Reply

2. Hi Rick, in my date text column there is values, but if I add the custom column the values return null

   Reply

3. Hi, what about English dates like 1st January 2024, 13th March 2021 or 3rd December 2022. Is there a way to convert those?

   Reply

   • Hi Jok,

     Ordinal Numbers are a little tricky. Power Query does not recognize them as such. One approach to achieve your desired outcome is:

     let
       dateString = "1st January 2024",
       offset = Text.PositionOf( dateString, " "),
       cleanDateString = Text.RemoveRange( dateString, offset - 2, 2 ),
       myDate = Date.From( cleanDateString  )
     in 
       myDate

     Reply

4. Hi Rick, thank you so much for your sharing.
   Hope you could help the below?
   I have the date time as below
   1/8/2023 3:19:00 AM
   1/8/2023 4:22:02 AM
   1/8/2023 4:22:03 AM
   1/8/2023 9:28:06 AM
   ….
   and want to convert into date.
   I’ve tried the below formula but still getting error

   = Table.AddColumn(
     #"Changed Type", 
     "Date", 
     each Date.FromText( [Resevation time],[Format="d\/m\/yyyy  hh\:mm\:ss AM/PM"] )
   )

   thank you.

   Reply

   • I also tried:

     Date.FromText([Resevation time],[Format= "%d/%m/yyyy hh:mm:ss AM/PM"]

     but still error
     Parameter.Error: We couldn’t convert the text value to date using the specified format. The format includes a time component.
     Details:
     Format=%d/%m/yyyy hh:mm:ss AM/PM

     hope you could help

     Reply

     • Hi!

       I know this can be quite confusing. The Date.FromText function does not support any time components as specified on this page: https://powerquery.how/time-totext/

       You could take multiple approaches.

       Either you can first turn the text into a datetime format with something like:

       DateTime.Date( 
         DateTime.FromText( 
           "1/8/2023 3:19:00 AM", 
           [Format = "d/M/yyyy %h:mm:ss tt"] 
           ) 
       )

       Alternatively, you could extract all characters before the first space in your string, and turn that string into a date.

       Date.FromText( 
           Text.BeforeDelimiter( "1/8/2023 3:19:00 AM", " " ),
           [Format = "M/d/yyyy"] 
       )

       When converting a date value to a text we can also insert arbitrary characters like:

        // Output: "January 31, 2024 will be on Wednesday"
       Date.ToText( 
         #date( 2024, 1, 31 ), 
         [ Format = "MMMM %d, yyyy' will be on' dddd" ] 
       )

       Unfortunately, the reverse does not work due to a bug in Power Query. That bug was reported a couple of weeks ago. Hope that helps!

       Reply

5. Hi Rick, thanks for the sharing. It is very useful

   Reply

6. Hi Rick,
   Hope you can help out here. I had a challenge at a job interview process where I was given a dataset with the date column in year & quarter and the column format as Time. I was unable to transform it into a proper Year-quarter.
   Part od the dataset below.
   Time
   201501
   201504
   201507
   201510

   Reply

   • Hi Herve. That’s an interesting challenge.

     You could try something like:

     let 
       value = 201504,
       textvalue = Text.From( value ),
       year =  Text.Start( textvalue, 4 ),
       month = Number.From( Text.End( textvalue, 2 ) ),
       quarter = Number.RoundAwayFromZero( month / 3 ),
       result = year & "-" & Text.From( quarter )
     in 
       result

     Reply

7. Thanks Rick for the quick response.

   however the Date Format column only contains errors.

   Reply

   • Hey Robert,

     That’s strange. For me everything seems to work. Could it be that your computer has a different language that requires a different notation of “yyyyMMdd”.

     If I had to guess your last name sounds german. Would for example “jjjjmmtt” work for you?

     Reply

8. Hi Rick,
   Thanks for this article. Pity you don’t have the samples for download.
   With the Date.FromText function I am running into errors as soon as I specify the Format argument, after trying for a long time it looks to me like a bug or something regional settings related.
   I have a simple sample file I can send so you see the effect …
   Essentially I have date colums with Text like “20220726” and I try to convert it with … each Date.FromText([Date Text], [Format=”yyyyMMdd”]), to a date value, this combination is causing an error, where as … each Date.FromText([Date Text]), returns a valid date. Also other formats (you used) cause errors … as soon as i use the Format argument
   Any ideas?
   Servus
   Robert

   Reply

   • Hi Robert,

     Can you give this code a try? You can copy-paste it into the advanced editor of a query and it should show you a working example.

     let
       Source = Table.FromRows(
         Json.Document(
           Binary.Decompress(
             Binary.FromText(
               "RYy7DoAgDEV/pWHmdctAGPkAF+NG2DVxMCb+vx
                SIJh16Tm9vKYo9MzhAVV1UgAEbUR1lETGvgg7s
                vrAF2y/sods09B2RUnRwIwrZmhjAtOSV5ltrkZ
                q/Z9sfTZHydR8nDVtf", 
               BinaryEncoding.Base64
             ), 
             Compression.Deflate
           )
         ), 
         let
           _t = ((type nullable text) meta [Serialized.Text = true])
         in
           type table [#"Date Text" = _t]
       ), 
       #"Added Custom" = Table.AddColumn(Source, "Date", 
                         each Date.FromText([Date Text]), type date), 
       #"Added Custom1" = Table.AddColumn(
         #"Added Custom", 
         "Date Format", 
         each Date.FromText([Date Text], [Format = "yyyyMMdd"]), 
         type date
       )
     in
       #"Added Custom1"

     Cheers,
     Rick

     Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.