Date.ToText

D

Back to top

T

Back to top

#

Back to top

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

Introduction to Power Query

Before we dive into the M code behind the Date.ToText function, let’s first take a quick look at what Power Query is and what it does. Power Query allows you to connect to a variety of data sources, including databases, Excel files, and CSV files, and then transform that data in a variety of ways.

Power Query is a visual interface that allows you to perform these transformations using a drag and drop interface. However, behind the scenes, all of these transformations are actually being performed using M code.

The Date.ToText Function

One of the most commonly used functions in Power Query is the Date.ToText function. This function takes a date as input and returns a text string representing that date in a specified format. The syntax for the Date.ToText function is as follows:

Date.ToText(dateTime as any, optional format as nullable text, optional culture as nullable text) as text

The first argument, `dateTime`, is the date that you want to format. The second argument, `format`, is an optional text string that specifies the format that you want to use. If you don’t specify a format, the function will use the default format for your region. The third argument, `culture`, is also optional, and specifies the culture that you want to use for formatting the date.

Examples of Date.ToText

Let’s take a look at some examples of the Date.ToText function in action. Suppose you have a date column in your data that looks like this:

| Date |

|------------|

| 01/01/2021 |

| 02/01/2021 |

| 03/01/2021 |

You can use the Date.ToText function to format these dates in a variety of ways. For example, the following formula will format the dates as “January 1, 2021”:

= Table.AddColumn(#”Previous Step”, “Formatted Date”, each Date.ToText([Date], “MMMM d, yyyy”))

This formula adds a new column to the table called “Formatted Date”, and uses the Date.ToText function to format the dates in the “Date” column.

You can also use the Date.ToText function to format dates in a variety of other ways. For example, the following formula will format the dates as “2021-01-01”:

= Table.AddColumn(#”Previous Step”, “Formatted Date”, each Date.ToText([Date], “yyyy-MM-dd”))

The M Code Behind Date.ToText

Now that we’ve seen some examples of how to use the Date.ToText function, let’s take a look at the M code behind this function. The M code for the Date.ToText function is as follows:

Date.ToText = (dateTime as any, optional format as nullable text, optional culture as nullable text) =>

let

sourceDate = DateTimeZone.RemoveZone(dateTime),

sourceCulture = if culture = null then CultureInfo.CurrentCulture else Culture.FromCultureName(culture),

formatString = if format = null then sourceCulture.DateTimeFormat.ShortDatePattern else format,

result = Text.Format(formatString, sourceCulture, sourceDate)

in

result

This code may seem a bit daunting at first, but let’s break it down. The first line defines the function and its arguments. The second line removes the time zone from the input date, as Power Query stores dates with time zones by default.

The next line checks to see if a culture was specified. If no culture was specified, it uses the current culture of the user’s machine. The next line checks to see if a format was specified. If no format was specified, it uses the short date format for the specified culture.

Finally, the code uses the Text.Format function to format the date using the specified format and culture. This is the same function used by the Format function in Excel.

The Date.ToText function is a powerful tool for formatting dates in Power Query. By understanding the M code behind this function, you can gain a better understanding of how Power Query works and how to use it to transform your data. Whether you need to format dates for reporting purposes or just want to clean up your data, the Date.ToText function is an essential part of any Power Query user’s 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)