How to Extract Dates From Text String in Power Query

This post shows how to extract dates from text values in Power Query. You may work with integers or text values that represent dates. For work with these values it is often desirable to convert the values to a date data type.

Yet sometimes Power Query is unable to transform a value to the desired data type. For these cases the approach shown in this article may help. So what approach can you use to convert a data type from text to date?

Automatically Recognizing Date Formats

A very versatile function for this is the Date.FromText function. As first argument you can provide the function with a text value. If your value is an integer, please make sure to transform it to a text value first.

After providing the function with your text, Power Query tries to convert your text to a date value. The following text values transform successfully to a date value:

= Date.FromText( "20221231" )     // Returns #date(2022, 12, 31)
= Date.FromText( "31-12-2022" )   // Returns #date(2022, 12, 31)
= Date.FromText( "2022-12-31" )   // Returns #date(2022, 12, 31)
= Date.FromText( "2022/12/31" )   // Returns #date(2022, 12, 31)
= Date.FromText( "31.12.2022" )   // Returns #date(2022, 12, 31)
= Date.FromText( "01,01,2022" )   // Returns #date(2022, 1 , 1 )
= Date.FromText( "1997/1/1" )     // Returns #date(1997, 1 , 1 )
= Date.FromText( "1/1/1997" )     // Returns #date(1997, 1 , 1 )
= Date.FromText( "12 MAR 22" )    // Returns #date(2022, 3 , 12)
= Date.FromText( "31..12..2022" ) // Returns #date(2022, 12, 31)
Extract date from text

When Power Query recognizes the text as a date, it automatically converts the string to a date. Many times however, text values are not recognized as date. Note that results may differ depending on the culture of your device. For example:

= Date.FromText( "31122022" )   // ddMMyyyy returns an Error
= Date.FromText( "12312022" )   // MMddyyyy returns an Error
= Date.FromText( "20223112" )   // yyyyddMM returns an Error
= Date.FromText( "12-31-2022" ) // MM-dd-yyyy returns an Error
= Date.FromText( "2022-31-12" ) // yyyy-dd-MM returns an Error
= Date.FromText( "31*12*2022" ) // dd*MM*yyyy returns an Error
= Date.FromText( "31.*12.*22" ) // dd.*MM.*yy returns an Error
Failed date conversion

Manually provide Date Format

To correctly transform a text string to a date value, you can make use of the optional options parameter in the second argument. You can input a formatting string (within a record) to instruct the function which format the text value has. All examples that returned an error before, work with the below formatting instructions.

= Date.FromText( "31122022",   [Format="ddMMyyyy"] )
// Returns #date(2022, 12, 31)
= Date.FromText( "12312022",   [Format="MMddyyyy"] )
// Returns #date(2022, 12, 31)
= Date.FromText( "20223112",   [Format="yyyyddMM"] )
// Returns #date(2022, 12, 31)
= Date.FromText( "12-31-2022", [Format="MM-dd-yyyy"] )
// Returns #date(2022, 12, 31)
= Date.FromText( "2022-31-12", [Format="yyyy-dd-MM"] )
// Returns #date(2022, 12, 31)
= Date.FromText( "31*12*2022", [Format="dd*MM*yyyy"] )
// Returns #date(2022, 12, 31)
= Date.FromText( "31.*12.*22", [Format="dd.*MM.*yy"] )
// Returns #date( 2022, 12, 31 )
Custom formatting for date conversion

Culture dependent Formatting

All examples before work regardless of the language your machine has. Yet sometimes a notation can be different depending on the culture. For example, when taking into account the month name.

In those cases you can provide the options parameter with a culture. When omitting this culture, the function by default uses the culture of your machine.

So what are examples that are culture dependent?

= Date.FromText( "30 Mrt 2022", [Format="dd MMM yyyy"] )
// Returns an error, PQ does not recognize Mrt as March

= Date.FromText( "30 Mrt 2022", [Format="dd MMM yyyy", Culture="nl-NL"] )
// Returns #date( 2022, 3, 30). Mrt = March in Dutch

= Date.FromText( "30 Maart 22", [Format="dd MMMM yy", Culture="nl-NL"] )
// Returns #date( 2022, 3, 30). Maart = March in Dutch

= Date.FromText( "30 juin 2022", [Format="dd MMM yyyy"] )
// Returns an error, PQ does not recognize juin as June

= Date.FromText( "30 juin 2022", [Format="dd MMM yyyy", Culture="fr-FR"] )
// Returns #date( 2022, 6, 30 ), juin = June in French

All these examples make use of centrally defined data formats. You can look at these links for additional information on Standard Date Formats and Custom Date Formats. The main useful formatting is:

// With a date of 02-01-2022 the following formats return: 

M = 1           // notice that capital M's represent months
MM = 01         // lowercase m's represent minutes
MMM = Jan
MMMM = January

d = 2-1-2022
%d = 2
dd = 02
ddd = Sun
dddd = Sunday

y = January 2022
yy = 22
yyy = 2022
yyyy = 2022

That was all for today!

Recommend Reading

>>> Power Query - Foundations <<<List Functions (200+ examples)Text functions (150+ examples)Creating Tables from Scratch (40+ examples)Understanding If Statements 
Generating a Date Table


>>>  Power Query - Advanced Topics <<<
Master List.Generate with Easy Examples
 Deep Dive into List.Accumulate
Create Calendar with Dynamic Language
Replacing Values (User Interface and Beyond)
Share on:
About Rick de Groot

Rick is the founder and editor of BI Gorilla. He believes learning is one of the great pleasures in life and wants to share his knowledge to help you improve your skills.

Learn more about him here, connect with him on Twitter, Facebook and LinkedIn and subscribe to his YouTube Channel.

5 thoughts on “How to Extract Dates From Text String in Power Query”

  1. 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
    • 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
  2. 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

Leave a comment