Date.FromText

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Date.FromText

What is the Date.FromText function?

The Date.FromText function is used to convert a text string into a date value. The function takes a text string as input and returns a date value. The syntax of the function is as follows:

Date.FromText(text as text, optional culture as nullable text)

- text: This is the text string that you want to convert to a date value.

- culture: This is an optional parameter that specifies the culture to use when converting the text string to a date value. If you don't specify a culture, the function will use the culture of your operating system.

How does the Date.FromText function work?

The Date.FromText function works by using a set of predefined date formats to parse the text string and convert it to a date value. The function tries each date format in turn until it finds a match. If none of the date formats match the text string, the function returns an error.

The predefined date formats that are used by the Date.FromText function depend on the culture that is specified. For example, if you specify the culture "en-US", the function will use the date formats that are commonly used in the United States. If you specify the culture "fr-FR", the function will use the date formats that are commonly used in France.

Here is an example of how the Date.FromText function works:

Date.FromText(“10/31/2021”)

In this example, the Date.FromText function will try to parse the text string "10/31/2021" using the predefined date formats. If the function is successful, it will return a date value of October 31, 2021.

The M code behind the Date.FromText function

The M code behind the Date.FromText function is relatively simple. The function first tries to parse the text string using the predefined date formats for the specified culture. If the function is successful, it returns a date value. If not, it returns an error.

Here is the M code for the Date.FromText function:

(text as text, optional culture as nullable text) =>

let

result = try Date.FromText(text, culture)

in

if result[HasError] then

error result[ErrorRecord][Message]

else

result[Value]

The M code uses the "try" function to attempt to parse the text string using the predefined date formats. If the function is successful, it returns a record that contains the date value. If not, it returns a record that contains an error message.

The "if" statement at the end of the M code checks if the "try" function returned an error. If it did, the M code returns the error message. If not, the M code returns the date value.

Examples of using the Date.FromText function

Here are some examples of using the Date.FromText function in Power Query:

Example 1

Convert the text string "10/31/2021" to a date value using the "en-US" culture:

Date.FromText(“10/31/2021”, “en-US”)

This will return a date value of October 31, 2021.

Example 2

Convert the text string "31/10/2021" to a date value using the "fr-FR" culture:

Date.FromText(“31/10/2021”, “fr-FR”)

This will return a date value of October 31, 2021.

Example 3

Convert the text string "2021/10/31" to a date value using the "en-US" culture:

Date.FromText(“2021/10/31”, “en-US”)

This will return an error, since the text string does not match any of the predefined date formats for the "en-US" culture.

The Date.FromText function is a powerful tool that can be used to convert text strings to date values in Power Query. By understanding the M code behind the function, you can gain a deeper understanding of how it works and how to use it effectively. Whether you are new to Power Query or an experienced user, the Date.FromText function is a valuable tool to have in your toolkit.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)