Date.IsInCurrentYear

D

Back to top

T

Back to top

#

Back to top

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

Understanding the Date.IsInCurrentYear Function

Before we dive into the M code behind Date.IsInCurrentYear, let’s first understand what this function does. This function takes in a single argument – a date value – and returns a Boolean value indicating whether the date falls within the current year or not.

Here’s the syntax for using this function:

Date.IsInCurrentYear(date as any) as logical

Let's take a look at a few examples to understand how this function works.

Example 1

Suppose we have a table of sales data that looks like this:

| Date | Sales |

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

| 2020-01-01 | 1000 |

| 2020-02-01 | 1500 |

| 2021-01-01 | 2000 |

| 2021-02-01 | 2500 |

If we want to filter this table to show only the sales data from the current year, we can use the following M code:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WMjQwVNJRMgYygQJQYGBgYGBgYGJgYmjgxo2aIaFmZmZgYA==”, BinaryEncoding.Base64)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Sales = _t])),

#”Changed Type” = Table.TransformColumnTypes(Source,{{“Date”, type date}, {“Sales”, Int64.Type}}),

#”Filtered Rows” = Table.SelectRows(#”Changed Type”, each Date.IsInCurrentYear([Date]))

in

#”Filtered Rows”

In this code, we first create a table from the rows of data, then transform the data type of the Date and Sales columns. Finally, we use the Table.SelectRows function to filter the data based on whether the date falls within the current year or not.

Example 2

Suppose we have a column of dates in Excel that we want to filter based on whether they fall within the current year or not. We can use Power Query to do this by following these steps:

1. Open Excel and select the column of dates.

2. Click on the "Data" tab and select "From Table/Range".

3. In the Power Query Editor, select the date column and click on the "Transform" tab.

4. Click on "Date Filters" and select "Is In Current Year".

5. Click on "OK" to apply the filter.

Behind the scenes, Power Query is using the Date.IsInCurrentYear function to filter the data based on the selected dates.

The M Code Behind Date.IsInCurrentYear

Now that we understand what the Date.IsInCurrentYear function does and how to use it, let's take a closer look at the M code behind this function.

Here's the M code for the Date.IsInCurrentYear function:

(date as any) as logical =>

let

currentYear = Date.Year(DateTime.LocalNow()),

yearOfDate = Date.Year(date)

in

yearOfDate = currentYear

In this code, we define a function that takes in a single argument - the date value. We then use the let statement to define two variables:

- currentYear: This variable stores the current year (obtained using the Date.Year and DateTime.LocalNow functions).

- yearOfDate: This variable stores the year of the input date value (obtained using the Date.Year function).

Finally, we use the = operator to compare the yearOfDate variable with the currentYear variable and return a Boolean value indicating whether they are equal or not.

The Date.IsInCurrentYear function is a useful tool for filtering data based on whether the date falls within the current year or not. By understanding the M code behind this function, we can gain a deeper understanding of how it works and how to use it effectively in our Power Query projects.

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)