How to Extract Dates From Text String in Power Query

This post shows how to transform a text string into a date 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!

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.

Leave a comment