DateTime.Date

D

Back to top

T

Back to top

#

Back to top

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

Introduction to DateTime.Date function

The `DateTime.Date` function is a part of the DateTime module in Power Query. It takes a datetime value as an input and returns a date value. The syntax for this function is as follows:

DateTime.Date(dateTime as datetime) as date

The `dateTime` parameter is the input datetime value that needs to be converted into a date value. The function returns the date portion of the input `dateTime` value.

Understanding the M code behind DateTime.Date

The M code behind the `DateTime.Date` function is quite simple. It uses the `Date.From` function to extract the date portion from the input datetime value. The M code for this function is as follows:

let

Source = (dateTime as datetime) => Date.From(dateTime)

in

Source

In the above M code, the `Source` variable is a function that takes a `dateTime` parameter and returns the output of the `Date.From` function. The `Date.From` function takes a datetime value as an input and returns the date portion of that value.

Examples of using DateTime.Date

Let's look at some examples of using the `DateTime.Date` function in Power Query.

Example 1 - Extracting date from a datetime column

Suppose we have a table `Sales` with a datetime column `DateSold`. We want to extract the date portion from this column and create a new column `DateSoldOnly` with just the date values. We can do this using the following M code:

let

Source = Table.TransformColumns(Sales,{{“DateSold”, DateTime.Date, type date}})

in

Source

In the above M code, we use the `Table.TransformColumns` function to transform the `DateSold` column by applying the `DateTime.Date` function to each value in the column. The resulting column is of type `date`.

Example 2 - Filtering data based on date

Suppose we have a table `Orders` with a datetime column `OrderDate`. We want to filter the data based on orders that were placed on or after January 1, 2021. We can do this using the following M code:

let

Source = Table.SelectRows(Orders, each [OrderDate] >= #date(2021,1,1))

in

Source

In the above M code, we use the `Table.SelectRows` function to filter the `Orders` table based on the condition that the `OrderDate` value is greater than or equal to January 1, 2021. We use the `#date` function to create a date value for January 1, 2021.

Example 3 - Creating a calendar table

Suppose we want to create a calendar table with all the dates between January 1, 2021 and December 31, 2021. We can do this using the following M code:

let

StartDate = #date(2021,1,1),

EndDate = #date(2021,12,31),

Dates = List.Dates(StartDate, Duration.Days(EndDate-StartDate)+1, #duration(1,0,0,0)),

#”Converted to Table” = Table.FromList(Dates, Splitter.SplitByNothing(), {“Date”}),

#”Changed Type” = Table.TransformColumnTypes(#”Converted to Table”,{{“Date”, type date}})

in

#”Changed Type”

In the above M code, we use the `#date` function to create date values for the start and end dates. We use the `List.Dates` function to create a list of dates between the start and end dates. We then convert this list into a table using the `Table.FromList` function and transform the `Date` column to type `date`.

The `DateTime.Date` function is a powerful tool for working with dates in Power Query. By understanding the M code behind this function, we can use it in various scenarios such as extracting date from datetime values, filtering data based on date, and creating a calendar table. With the help of this function, we can easily manipulate and analyze date-related data in Power Query.

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)