Power Query Problems

Format Dates as MMM-YY in Power BI Using DAX or Power Query

Anum MushtaqAnum Mushtaq Nov 22, 2026 868 views

Question details

Format a date as an abbreviated month and two-digit year in Power BI.

Format Dates as MMM-YY in Power BI Using DAX or Power Query
Product
Microsoft Power BI
Device & OS
not provided
Scenario
The user wants a date column to display values such as Jan-24.
Observed behavior
A DAX FORMAT formula produces a syntax error when entered in a Power Query custom column.
Before you start

Check whether you are creating a column in Power Query Editor or in the Power BI data model. Confirm that the source column is a date and note its table and column names.

Solution 1Recommended

Use an M formula in a Power Query custom column

Use this method when adding the column in Power Query Editor, where DAX formulas are not recognized.

1
Open Power Query Editor

In Power BI Desktop, select Home > Transform data, then select the query containing the date column.

2
Check the date type

Select the Date column and use the Data Type control to set it to Date if needed.

3
Add a custom column

Select Add Column > Custom Column, enter a name such as Month-Year, and enter Date.ToText([Date], "MMM-yy") in the formula box. Replace Date with your column name.

4
Apply the change

Select OK, check the resulting values, and select Home > Close & Apply.

Use an M formula in a Power Query custom column
Month names and locale: Month abbreviations depend on the culture used. For English abbreviations, use Date.ToText([Date], [Format="MMM-yy", Culture="en-US"]).
Free Microsoft Office alternative

Format Spreadsheet Dates Easily with WPS Office

WPS Office is a free, lightweight alternative for working with spreadsheets and other Office documents. Its familiar interface and Microsoft Office format compatibility make it easy to move existing files, although it does not fix Power BI formulas.

Format spreadsheet dates with custom month-year display formatsOpen and edit Microsoft Excel XLSX filesWork with familiar spreadsheet controls and formulasMove existing Office documents into a lightweight suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does Power Query not recognize FORMAT?

Power Query custom columns use the M language, not DAX. In Add Column > Custom Column, use Date.ToText([Date], "MMM-yy") instead.

Where can I enter the DAX FORMAT formula?

In Power BI Desktop, select the relevant table and choose Modeling > New column. Enter a calculated-column formula such as Month-Year = FORMAT(Data[Date], "MMM-YY").

How can I show English month abbreviations in Power Query?

In the Custom Column formula box, use Date.ToText([Date], [Format="MMM-yy", Culture="en-US"]) to specify the English US culture.

Will the formatted month-year column sort chronologically?

The formatted result is text, so it may sort alphabetically. In the data model, create a chronological sort column, select the month-year column, and use Column tools > Sort by column to choose it.