Power Query Problems

How to Create Persian Calendar Date Formulas in Power Query

Maira Mehtab Sep 20, 2026 868 views

Question details

The user needs guidance on creating date-related formulas in Power Query to work with Persian calendar dates and extract localized Persian month names.

Product: Power Query
Device & OS: not provided
Scenario: Analyzing dataset dates and needing to extract or display Persian calendar information within Power Query.
Observed behavior: Power Query defaults to the Gregorian calendar, and native date functions do not directly align with the Persian calendar, requiring culture arguments or custom logic.

Before you start

Verify that your base date columns are properly formatted as 'Date' or 'Date/Time' data types in Power Query before applying culture-specific formulas.

Solution 1Recommended

Use Culture Arguments for Month Names

Apply the "fa-IR" culture argument in supported Power Query M language functions to extract localized Persian month names.

Many Power Query date functions accept a secondary culture argument. While this does not mathematically convert Gregorian dates to Persian dates, it allows you to natively extract localized text, such as Persian month names, from standard dates.

Select the target column

Open the Power Query Editor and click on the column containing your standard date values.

Add a Custom Column

Navigate to the 'Add Column' tab on the ribbon and click on 'Custom Column'.

Enter the M formula

In the Custom Column dialog, enter the formula: = Date.MonthName([YourDateColumnName], "fa-IR")

Apply and verify

Click 'OK' to create the column. You should now see the localized Persian month names corresponding to the dates.

Function Limitations: This method relies on the underlying Microsoft Date.MonthName documentation. It simply translates the Gregorian month of that date into Persian text, which may not align with the actual Persian calendar month.

Solution 2

Implement a Custom Calendar Mapping Table

Use a pre-calculated mapping table to accurately convert between Gregorian and Persian dates.

Free Microsoft Office alternative

Try WPS Office for Seamless Spreadsheet Management

While complex M language transformations like Power Query culture arguments are specific to Microsoft Excel, WPS Office provides a powerful, lightweight, and completely free alternative for handling massive datasets, utilizing advanced formulas, and creating comprehensive reports.

1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite.
2. Open WPS Spreadsheets: Launch the application and select 'Spreadsheets' from the main dashboard.
3. Import your data: Open your existing Excel workbooks seamlessly to continue analyzing your calendar and date data without formatting loss.

Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) file formats.Built-in advanced date and time functions for flexible data analysis.Lightweight installation with a familiar, highly intuitive user interface.Completely free to download and use for your daily office tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Can Power Query automatically convert Gregorian dates to Persian dates?

No, Power Query does not have a native function to automatically translate Gregorian dates into exact Persian (Jalali) calendar dates because the two calendars are not synchronous. You must use custom M code or a date mapping table.

What is the correct culture code for Persian in Power Query?

The culture code for Persian (Iran) used in Power Query is "fa-IR". You can pass this string as the secondary culture argument in supported text and date functions to get localized outputs.

Why do Persian month names show errors in my custom column?

Errors typically occur if the base column is not explicitly formatted as a Date type, if it contains null or invalid values, or if the specific M function you are using does not accept a culture argument. Ensure your formula is strictly formatted as Date.MonthName([Date], "fa-IR").

Excel & Data Problems