You may see dates in your Power Query results incorrectly displayed as 12/31/1899. This happens when Power Query converts a blank or zero-value cell into a date. The system interprets the zero as the starting point for its date serial number system. This article explains why this conversion error occurs and provides clear steps to correct it.
Key Takeaways: Fixing the 12/31/1899 Date Error
- Change column data type to Text: Prevents Power Query from misinterpreting blank or zero values as the date serial number zero.
- Replace null or zero values: Use the Replace Values feature to swap problematic values for a true blank before converting to a date.
- Use the Table.TransformColumnTypes function: Apply advanced M code to handle conversion errors gracefully and replace them with null.
Why Power Query Displays Zero as 12/31/1899
Excel and Power Query use a date serial number system where each date is stored as a number. The base date, or day zero, in this system is December 30, 1899. The number 1 represents December 31, 1899. When Power Query imports data, it tries to detect the correct data type for each column. If a column contains mostly dates but also has blank cells or cells with a value of zero, Power Query may assign the Date data type to the entire column.
The problem arises because a true blank in the source data is read as a null value, but a cell containing a zero is read as the number zero. When Power Query tries to convert the number zero to a date, it returns the date serial for day zero, which is 12/31/1899. This is not a bug but a consequence of how the date system is designed. The fix involves cleaning the data before the type conversion happens.
Steps to Correct the 12/31/1899 Date Error
Follow these methods to clean your data and prevent the zero-date conversion. Start with the simplest method first.
Method 1: Change Data Type to Text First
- Load your data into Power Query Editor
Select your data table in Excel, then go to the Data tab and click From Table/Range. This opens the Power Query Editor window. - Change the problematic column to Text type
Click the data type icon next to the column header. It may show Date or Any. From the dropdown menu, select Text. This action prevents any automatic numeric interpretation of the values. - Clean the zero and blank values
Click the dropdown arrow in the column header. Uncheck the (null) and 0 values if they are listed, or use the Text Filters to remove them. Alternatively, you can use the Replace Values tool. - Convert the cleaned column to Date type
After removing zeros and nulls, click the data type icon for the column again. This time, select Date. Only the valid text entries will be converted, avoiding the 1899 error. - Close and load the query
Click Home > Close & Load. Your corrected data will be loaded into a new worksheet without the incorrect dates.
Method 2: Use Replace Values Before Converting
- Open the Power Query Editor
Ensure your data with the date column is loaded into the editor. - Select the column and open Replace Values
Right-click the header of the date column. Choose Replace Values from the context menu. The Replace Values dialog box will appear. - Replace zero with a null placeholder
In the Value To Find field, type 0. Leave the Replace With field empty. Click OK. This changes all zeros to null values, which Power Query handles differently. - Convert the column to the Date type
Now, change the column data type to Date. The null values will remain as blanks, and valid numbers will convert to correct dates.
If the Date Error Persists After Cleaning
Power Query Still Shows 1899 After Changing Type
If the error remains, the applied steps may be in the wrong order. In the Power Query Editor, look at the Applied Steps pane on the right. The step Changed Type should come after any cleaning steps like Replaced Value. You can drag steps in this pane to reorder them. Ensure Replace Values or Filtered Rows happens before the type conversion to Date.
Source Data Has Hidden Zero Values or Formulas
Your original Excel table might contain cells that look blank but have a formula returning an empty string or a zero. Power Query reads the formula result, not the displayed cell. Before importing, convert these formulas to static values. Copy the column in your source sheet and use Paste Special > Values to remove the formulas. Then refresh your Power Query connection.
Using Advanced Editor for Error Handling
For full control, you can use M code in the Advanced Editor. You can modify the type conversion step to handle errors. Look for a line of code like Table.TransformColumnTypes(#"Previous Step",{{"YourColumn", type date}}). You can replace it with a more robust version that replaces errors with null: Table.TransformColumnTypes(#"Previous Step",{{ "YourColumn", type date}}, "en-US"). While this specific function doesn’t catch all errors, it ensures proper regional formatting. For complex error trapping, you may need to add a custom column with a try…otherwise expression.
Data Type Conversion Methods: Comparison
| Item | Change Type via UI | Replace Values First | Advanced M Code |
|---|---|---|---|
| Best for | Simple datasets with obvious blanks | Datasets containing explicit zero values | Complex, recurring data pipelines |
| Complexity | Low | Medium | High |
| Error Handling | Converts zero to 12/31/1899 | Prevents error by replacing zero with null | Can be written to catch and manage all conversion errors |
| Step Order Dependency | Critical – must clean data first | Critical – replace must happen before type change | Flexible – logic is embedded in the code step |
| Refresh Reliability | May fail if new zeros appear in source | Reliable if replace logic covers all cases | Most reliable if code is written correctly |
You can now fix the 12/31/1899 date error by changing your column to Text first or using the Replace Values tool. Always ensure data cleaning steps occur before the final type conversion to Date in your Applied Steps pane. For automated reports, consider adding a Replace Values step that targets zero to make your query robust against future data entry errors. Use Ctrl + Click to select multiple column headers if you need to apply the same fix to several date columns at once.