Date.ToRecord

D

Back to top

T

Back to top

#

Back to top

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

Understanding Date Values in Power Query

Before we dive into the M code for Date.ToRecord, it’s important to have a basic understanding of how dates are represented in Power Query. In Power Query, dates are stored as serial numbers, with each day represented by a whole number starting from 1 (January 1st, 1900). For example, January 1st, 2010 would be represented as the serial number 40179.

While these serial numbers might seem arbitrary, they allow for easy manipulation and calculation of dates within Power Query. For example, adding 1 to a date serial number will result in the next day, and subtracting 7 will result in a date one week earlier.

The M Code Behind Date.ToRecord

Now that we understand how dates are represented in Power Query, let’s take a look at the M code behind the Date.ToRecord function:

(date as date) as record =>

[

Year = Date.Year(date),

Month = Date.Month(date),

Day = Date.Day(date)

]

The code above defines the Date.ToRecord function, which takes a single argument - the date value that we want to convert to a record. The function then returns a record with three fields: Year, Month, and Day.

Let's break down the code in more detail. The first line of the code specifies the input parameter for the function - in this case, a date value. The "as record" at the end of the function definition specifies the output type of the function - in this case, a record.

The next few lines of code are where the real work happens. The function uses three built-in M functions - Date.Year, Date.Month, and Date.Day - to extract the year, month, and day from the input date value. These values are then used to create a new record with fields for Year, Month, and Day.

Using Date.ToRecord in Your Data Transformations

Now that we understand the M code behind the Date.ToRecord function, let's look at how we can use it in our own data transformations. Here are a few examples:

Example 1: Converting Dates to Records

Suppose we have a table with a column of date values, and we want to convert each date to a record with fields for Year, Month, and Day. We can use the following M code:

let

Source = Table.FromRows({

{#date(2021, 1, 1)},

{#date(2021, 1, 2)},

{#date(2021, 1, 3)}

}, {“Date”}),

#”Converted to Records” = Table.TransformColumns(Source, {“Date”, Date.ToRecord})

in

#”Converted to Records”

The M code above creates a table with three date values, and then uses the Table.TransformColumns function to apply the Date.ToRecord function to each value in the "Date" column. The resulting table has three records, each with fields for Year, Month, and Day.

Example 2: Filtering by Year

Suppose we have a table with a column of date values, and we want to filter the table to only include rows where the year is 2021. We can use the following M code:

let

Source = Table.FromRows({

{#date(2021, 1, 1)},

{#date(2021, 1, 2)},

{#date(2020, 12, 31)}

}, {“Date”}),

#”Converted to Records” = Table.TransformColumns(Source, {“Date”, Date.ToRecord}),

#”Filtered Rows” = Table.SelectRows(#”Converted to Records”, each [Year] = 2021)

in

#”Filtered Rows”

The M code above creates a table with three date values, and then uses the Table.TransformColumns function to apply the Date.ToRecord function to each value in the "Date" column. The resulting table has three records, each with fields for Year, Month, and Day. We then use the Table.SelectRows function to filter the table to only include rows where the "Year" field is equal to 2021.

In this article, we've explored the M code behind the Date.ToRecord function in Power Query. We've learned how dates are represented in Power Query, how the Date.ToRecord function works, and how to use it in our own data transformations. By mastering these concepts, you'll be well on your way to becoming a Power Query expert.

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)