Written By Rick de Groot

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

Are you tired of manually trying to extract a date from text values in Power Query? It can be a real headache, especially when dealing with different date formats and culture codes. But this article is here to show you a secret weapon in Power Query – the Date.FromText function.

With this function, you’ll be able to automatically extract dates from text, extract a date based on a specific format, and even retrieve a date based on a format and culture code.

Say goodbye to the tedious task of manually extracting dates and hello to a more efficient and accurate process. So, if you’re ready to up your Power Query game, keep reading and learn how to extract dates like a pro.

So what approach can you use to convert a data type from text to date?

Table of contents
Extract Dates From Text Strings in Power Query

Introducing Date.FromText

A very versatile function for turning text into a date 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.

The Date.FromText function automatically tries to recognize the date format a text value has, making it a very powerful tool for data analysis. With this function, you can automatically extract dates from text, extract a date based on a specific format, and even retrieve a date based on a format and culture code.

So what does that work?

Auto Recognize Dates

One thing that’s really cool about the Date.FromText function is that it can automatically try to recognize a date format from a text value.

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

Manual 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

Provide Culture Code

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

Conclusion

Well, there you have it! You now know how to extract dates from text values in Power Query using the Date.FromText function. You learned how to automatically extract dates from text, extract a date based on a specific format, and even retrieve a date based on a format and culture code. This method makes life so much easier when working with dates hidden in text values.

Did you know there are easier ways to create a date table? Your can use the methods described here to generate a calendar from scratch.

Now, I would like to ask you, have you tried using the Date.FromText function to extract dates from text values in Power Query? And were there use-cases not described in this article? If so, share your experience in the comments below. If not, give it a try and let me know how it went.

Happy querying!

Share on:

Latest from my blog

  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

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