Power Query Problems

How to Fix Planner Date Fields Not Recognized in Power BI and Excel

Phi Hung Vo Sep 27, 2026 869 views

Question details

The user needs to correctly parse and convert exported Microsoft Planner date fields from text format into recognized Date objects in Power BI.

How to Fix Planner Date Fields Not Recognized in Power BI

Product: Power BI and Excel
Device & OS: not provided
Scenario: Exporting project data from Microsoft Planner to Excel and importing it into Power BI for analysis and DAX calculations.
Observed behavior: Date fields are stored as text with locale-specific formatting or contain blanks, causing Power BI to not recognize them as dates. Changing the column format natively in Excel does not resolve the data type mismatch.

Before you start

Verify the original regional format (locale) of the exported Planner data, specifically noting whether the text dates are formatted as MM/DD/YYYY (US) or DD/MM/YYYY (UK).

Solution 1Recommended

Transform Data Type Using Locale in Power Query

Use the 'Using Locale' feature in Power Query Editor to correctly interpret text dates based on their original regional settings.

When Microsoft Planner exports data, dates are often rendered as text strings matching the creator's browser locale. Standard data type conversions might fail if your system locale differs from the exported data.

Clean Data and Remove Blanks

Open the Power Query Editor. Select your Planner date column, right-click, and choose 'Replace Values' to change blank or invalid values to 'null'. You should also trim any leading or trailing spaces.

Apply Locale Transformation

Right-click the date column header, navigate to 'Change Type', and select 'Using Locale...' from the dropdown menu.

Configure Data Type and Region

In the pop-up window, set the Data Type to 'Date' and choose the Locale that exactly matches the format of the exported text values (e.g., English (United States)). Click OK.

Transform Data Type Using Locale in Power Query

Verify Output Type: Always confirm that the query output type icon next to the column header has changed to a calendar icon before loading the data to create DAX calculations.

Solution 2

Apply Custom Conversion Using M Formula

Manually force text-to-date conversion using the Date.FromText function in an advanced custom column.

Free Microsoft Office alternative

Clean Exported Data Easily with WPS Office

If you are struggling with messy CSV or Excel exports from Microsoft Planner, WPS Office provides a lightweight, highly compatible alternative to Microsoft Office. Use its intuitive spreadsheet tools to clean and format text dates perfectly before importing them into your analytics software.

1. Open Exported File: Launch WPS Spreadsheet and open your exported Planner dataset.
2. Use Text to Columns: Select the problematic date column, navigate to the Data tab, and use Text to Columns to correctly parse the dates.
3. Save for Analytics: Save the cleaned file as a standard .xlsx document, ready for seamless import into your analytics tools.

Highly compatible with Microsoft Excel (.xlsx and .csv) formatsFree and lightweight alternative to heavy Microsoft Office installationsFamiliar user interface makes data cleaning simple and fast

microsoft office alternative - wps office

Frequently Asked Questions

Why are my Microsoft Planner dates exporting as text in Excel?

Microsoft Planner exports dates as text strings based on the regional settings of the browser used during export. This prevents standard spreadsheet software and Power BI from automatically recognizing them as valid date objects.

Can I just change the cell format to 'Date' in Excel to fix this?

No. Changing the cell format only alters how valid dates are displayed. If the underlying data is stored as text, formatting the column will not convert the data type. You must use tools like Power Query or 'Text to Columns' to re-parse the text into date values.

What should I do if my date column contains blank cells?

Before converting the data type to Date in Power Query, it is highly recommended to replace blank cells or invalid strings with 'null'. Otherwise, the date conversion step may yield errors for those specific rows.

Excel & Data Problems