Power Query Get Current Year: M Code and Refresh Tips

To get the current year in Power Query, use Date.Year(DateTime.LocalNow()); it returns the year as a whole number, such as 2026. For a consistent value throughout one query evaluation, use Date.Year(DateTime.FixedLocalNow()) instead.

What the current-year expression returns

Power Query uses the M language, whose date and time functions are different from Excel worksheet functions. DateTime.LocalNow() gets a current date-and-time value, and Date.Year(...) extracts its year. The result is a number, not text and not a date. That makes it suitable for comparisons, filters, and calculations that expect a numeric year.

For example, if the current date is in 2026, the expression returns 2026. It does not return a date such as January 1, 2026, and it does not read a year from one of your source columns. It asks for the date and time at the point the query is evaluated.

A simple complete query can be written in the Power Query Advanced Editor as:

let
    CurrentYear = Date.Year(DateTime.FixedLocalNow())
in
    CurrentYear

This query returns one value. If you only need the year inside a larger query, define it as a step in that query rather than creating a separate output query.

Create a query that returns the year

In Excel for Microsoft 365 on Windows, open Data > Get Data > From Other Sources > Blank Query. In the Power Query Editor, select Home > Advanced Editor, replace the existing text with the short query above, and select Done. The preview should show a single number. To put the result on a worksheet, select Home > Close & Load; use the Close & Load options if you want to choose a destination or load it as a connection instead.

You can also create the query from the Power Query Editor’s formula bar, but the Advanced Editor is clearer for a query consisting of a few named steps. If the formula bar is hidden, turn it on from the View tab. On a Mac, Power Query availability and ribbon options depend on the Excel version and build; if the Blank Query or Advanced Editor commands are not present, use a supported Windows or Excel-for-the-web workflow rather than assuming the Windows path exists on that Mac installation.

When the result is loaded to a worksheet, Excel displays it as a one-cell table by default. The query itself still returns a number. If you want to reuse the value in worksheet formulas, refer to the loaded cell or table column; do not expect an M query step to act as a worksheet formula automatically.

How the M functions work

DateTime.LocalNow() returns the current local date and time as a datetime value. Date.Year extracts the year component from that value. The shorter expression is valid, but it can evaluate the current time whenever that expression is evaluated. In most reports, that difference is immaterial. When several steps need the same year, assigning it once to a named step makes the intent clear and avoids requesting the clock repeatedly.

DateTime.FixedLocalNow() returns a local datetime that remains fixed during a single evaluation of the query. It is useful when a query performs many row operations and should use one consistent year for the whole evaluation. It does not permanently fix the year in the workbook: a later refresh evaluates the query again and can return a different year.

Because Date.Year returns a number, avoid converting the year to text unless a later step specifically needs text. Keeping it numeric makes a comparison such as [Year] = CurrentYear a number-to-number comparison and avoids issues such as comparing the text value "2026" with the number 2026.

Use the year to filter rows

A common reason to get the current year is to keep only records from that year. If your source has a properly typed date column named OrderDate, create a current-year step and use it in a row filter. For example, with a previous step named Source and dates in OrderDate:

CurrentYear = Date.Year(DateTime.FixedLocalNow()),
CurrentYearRows = Table.SelectRows(Source, each Date.Year(Date.From([OrderDate])) = CurrentYear)

In a complete query, place those assignments between let and in, and make the final in line return CurrentYearRows. Date.From makes the intended date extraction explicit when the source field contains datetimes. If the source column is already typed as Date, the conversion may be unnecessary.

For a datetime column, another approach is to compare it with the start of this year and the start of next year. That describes the range as “on or after January 1 this year, but before January 1 next year,” so times later on December 31 are included. Build the boundaries from the same captured year, using datetime boundaries when the column is typed as Date/Time. This range pattern can be preferable for large database sources because applying a year-extraction function to every row may make it harder for a connector to push the filter back to the source. Whether a filter folds depends on the connector and the surrounding steps, so check the query’s folding indicators or source-side query plan where available.

Check the column’s data type before filtering. If dates are actually text, Power Query may fail to convert them or interpret ambiguous values according to a locale. Set the correct type and, for text dates with a known format, use the appropriate locale-aware conversion rather than assuming that day and month order is universal.

Current year is not the same as a date’s year

“Get current year” can mean two different things. The current year is the year at refresh time, obtained from the clock. The year of a record is calculated from that record’s date, for example Date.Year([OrderDate]). These values are not interchangeable. A report filtered to the current year should compare the year in each row with a separately captured current year; extracting the year from each row alone does not make the filter dynamic.

For example, a table can contain orders from 2024, 2025, and 2026. The expression Date.Year([OrderDate]) produces the year for each order. The expression Date.Year(DateTime.FixedLocalNow()) produces one year for the refresh. Comparing the two keeps orders whose date falls in the current year, without hard-coding a value that would need manual editing next January.

Also distinguish a calendar year from a fiscal year. The functions above return the calendar year, which changes on January 1. If an organization’s fiscal year begins in another month, a fiscal-year calculation needs an offset or a business-specific rule. Do not label the calendar-year result as a fiscal year unless the organization’s fiscal calendar uses the same dates.

When the value changes and how to refresh

A query does not continuously update just because the clock moves forward. It evaluates when Power Query runs, typically during a refresh or when a dependent query is evaluated. If a workbook stays open across midnight on December 31, a displayed result can remain the old year until the query refreshes. Refresh the query to recalculate it; in Excel for Windows, Data > Refresh All refreshes the workbook’s connections and queries.

If the workbook should update when opened, inspect the query’s connection properties. In Excel, open Data > Queries & Connections, right-click the query or its connection, select Properties, and look for the option to refresh data when opening the file. Exact property availability can vary by connection and Excel build. Automatic refresh settings affect when the query runs; they do not turn the result into a live clock or guarantee an update at a particular time while the workbook is open.

Refresh timing matters around New Year. A query refreshed before midnight may return the old year, while a refresh after midnight in its effective time zone may return the new one. For scheduled reports, decide which time zone and refresh schedule define the reporting date, rather than relying on an assumption that every refresh occurs at the same local hour.

Local time, UTC, and online refresh

The word “local” needs attention when a workbook is refreshed in different environments. In Power Query Desktop, DateTime.LocalNow() and DateTime.FixedLocalNow() use the computer’s local date and time. In Power Query Online and cloud-based refresh environments, the corresponding local-now functions return UTC rather than the user’s personal computer time. Near midnight on January 1, those clocks can therefore produce different years.

This is not a defect in the year function; it reflects that “current” depends on a time zone. If the report must follow a particular business time zone, establish the date according to that requirement and account for the environment where refresh runs. Power Query M also has UTC-related date/time functions, but UTC is not automatically equivalent to a business’s local date. A fixed UTC offset is not a safe substitute for a named time zone where daylight-saving changes apply.

For a workbook refreshed only on one desktop in one time zone, local time is usually the intuitive choice. For a shared or cloud-refreshed dataflow, document the time basis and test the refresh around the year boundary. That makes a one-year discrepancy much easier to explain and prevents different refresh environments from silently applying different definitions.

Common mistakes and troubleshooting

Using an Excel formula in the M editor: Excel’s =YEAR(TODAY()) belongs in a worksheet cell. In Power Query, use M syntax such as Date.Year(DateTime.FixedLocalNow()).
Expecting a live update: the year changes when the query is evaluated, not continuously. Refresh the query or configure an appropriate refresh-on-open behavior.
Comparing number and text: keep the year numeric on both sides of a comparison, or convert deliberately if the source year is text.
Getting errors from a date column: inspect the column’s type and look for text, errors, or inconsistent date formats. Convert the source values before applying date functions.
Seeing an unexpected year online: check whether the refresh used UTC rather than the desktop’s local time, especially near midnight or New Year.
Filtering the wrong year: confirm whether the requirement is calendar year, fiscal year, or the year contained in each row’s date.

When troubleshooting, inspect the query preview after refresh and select the relevant step in Applied Steps. If an error appears only after the filter step, check nulls and invalid source values in the date column. If the output is correct in Desktop but differs after a cloud refresh, compare the refresh time and time-zone basis before changing the M expression.

Alternatives and choosing the right method

If the only goal is to show the current year in a worksheet cell, an Excel formula may be simpler: with a blank cell selected, enter =YEAR(TODAY()). This recalculates according to Excel’s calculation behavior and is not a Power Query result. Use Power Query when the year needs to drive an imported-data transformation, such as a filter or a generated column, or when the output should be refreshed as part of the query workflow.

A manually entered year is appropriate only when the reporting period is intentionally fixed. It can make an archived report reproducible, but it will not roll forward automatically. A current-year expression is convenient for recurring reports, but it can make a historical refresh return a different set of rows next year. If reproducibility matters, preserve the reporting year as an explicit parameter or record it with the report rather than assuming that “current year” will always mean the year used when the report was first created.

In short, use DateTime.FixedLocalNow() for one consistent clock value within a query evaluation, use a date-column expression to identify each record’s year, and choose a refresh and time-zone policy that matches the report’s purpose.

Frequently Asked Questions

What is the simplest Power Query formula for the current year?
Use Date.Year(DateTime.LocalNow()). It returns the current year as a number. For a value that stays fixed throughout one query evaluation, use Date.Year(DateTime.FixedLocalNow()).

Does Power Query update the current year automatically?
No. It recalculates when the query is evaluated, commonly on refresh. A query result can remain unchanged while a workbook is open, so refresh it when you need a new value.

How do I filter a table to the current year?
Capture the current year in a query step, then filter rows by comparing it with the year extracted from the date column. Make sure the column is a real Date or Date/Time value, not unconverted text.

Why does my online refresh return a different year from Excel Desktop?
Desktop local-now functions use the computer’s local time, while Power Query Online returns UTC for local-now functions. The difference can affect the year near midnight or at the turn of the year.

Can I use TODAY or YEAR in Power Query?
TODAY() and YEAR() are Excel worksheet function names, not the M expression for this task. Use Date.Year(DateTime.LocalNow()) in Power Query, or use =YEAR(TODAY()) in a worksheet cell.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.