Excel Power Query Date Column Changes Type After Refresh: Fix

You refresh a Power Query in Excel and a date column suddenly shows numbers or text instead of dates. This happens when Power Query re-evaluates the column data type based on the first thousand rows of the new source data. If those rows contain non-date values or blank cells, Power Query may guess a different type. This article explains why the type changes and provides a permanent fix to lock the date column type so it stays consistent after every refresh.

Why Power Query Changes the Date Column Type After Refresh

Power Query uses a feature called type detection. When you first load data, it samples the first 1,000 rows to guess the data type for each column. If your date column has a date in the first row, Power Query sets it to the Date type. After a refresh, the source data may have changed. For example, a new row at the top might contain a blank cell or a text value like “N/A.” Power Query samples again and may assign a different type, such as Text or Any. This causes the column to display numbers or text instead of dates.

Another common cause is a merged or appended query where the source tables have different column structures. If one table has a Date column and another has a Text column with date-like strings, Power Query may unify the type to Text. The same issue occurs when you import data from CSV files or databases where the column type is not explicitly defined.

The root problem is that Power Query re-evaluates types at each refresh unless you force a specific type in the M code. Simply clicking the Date icon in the editor does not always lock the type permanently. You need to change how the type is applied in the query steps.

Steps to Lock the Date Column Type Permanently

The following method uses a custom column with the Date.From function to ensure the column always contains date values. This approach works even if the source data has blanks or text in the date column.

1. Open the Power Query Editor
   In Excel, select your query from the Queries & Connections pane. Right-click the query and choose Edit. The Power Query Editor window opens.
2. Add a Custom Column
   Go to the Add Column tab on the ribbon. Click Custom Column. In the dialog, enter a name for the new column, such as “DateFixed.” In the formula box, enter Date.From([YourDateColumnName]). Replace YourDateColumnName with the actual name of your date column. Click OK.
3. Remove the Original Date Column
   Select the original date column by clicking its header. Right-click and choose Remove. The new column now holds the date values.
4. Set the Data Type of the New Column
   Click the data type icon next to the column header. Choose Date from the drop-down list. This step is optional but helps Excel display the values correctly.
5. Close and Load the Query
   Go to the Home tab and click Close & Load. The query refreshes and loads the data into Excel. The date column now uses the Date.From function on every refresh.

Alternative Method: Edit the M Code Directly

If you prefer to keep the original column name, you can edit the M code to force the type. In the Power Query Editor, go to the View tab and click Advanced Editor. Find the step that sets the column type. It looks like Table.TransformColumnTypes(#"Previous Step",{{"DateColumn", type date}}). Replace type date with Date.Type and add a try expression to handle errors. A safer version is:

Table.TransformColumnTypes(#"Previous Step",{{"DateColumn", each Date.From(_), type date}})

This code tells Power Query to convert the column using Date.From and then set the type to date. It overrides the automatic type detection.

If the Date Column Still Changes Type After the Fix

Power Query Shows an Error When Using Date.From

If the source data contains values that cannot be converted to a date, the Date.From function returns an error. To handle this, use try Date.From([DateColumn]) otherwise null in the custom column formula. This returns null for invalid values instead of breaking the query.

The Column Type Resets to Text After Merging Queries

When you merge two queries, Power Query may unify the type of the merged column to Text. Apply the custom column step after the merge operation. In the Power Query Editor, select the step after the merge, then add the custom column. This ensures the date conversion runs on the final dataset.

Power Query Automatically Changes the Type on Import from CSV

When importing CSV files, Power Query may misinterpret date columns as Text if the first 1,000 rows contain empty cells. Use the Transform tab and select Detect Data Type after applying the custom column. Alternatively, change the import settings to Data Type Detection > Do not detect data types in the Power Query Editor home tab before loading.

Manual Type Setting vs Custom Column with Date.From: Key Differences

Item	Manual Type Setting	Custom Column with Date.From
How it works	Power Query sets the column type based on the first 1,000 rows	Power Query converts every row using the Date.From function
Stability on refresh	Can change if source data varies	Always returns a date or null
Error handling	Power Query may change type to Text if it finds non-date values	You can add try/otherwise to handle invalid values
Performance	Fast, no extra calculation	Slightly slower due to row-by-row conversion
Best for	Stable source data with no blanks or text in date column	Unstable source data, merged queries, or CSV imports

You can now fix a date column that changes type after refresh in Power Query. Start by adding a custom column with Date.From and removing the original column. If errors appear, add try and otherwise null to the formula. For advanced control, edit the M code directly with Table.TransformColumnTypes and a custom function. As a next step, explore the Table.TransformColumnTypes documentation to learn how to apply type locking to multiple columns at once.