How to Convert Mixed Serial Numbers and Dates in Power Query
Question details
The user needs to standardize a column in Power Query that contains a mix of text-based dates (e.g., dd/mm/yyyy) and Excel serial date numbers into a single valid date format.
- Product
- Power BI / Power Query
- Device & OS
- not provided
- Scenario
- Importing or transforming data in Power Query where a date column has inconsistent formatting from the source dataset.
- Observed behavior
- The column data type cannot be straightforwardly changed to 'Date' because it contains both text date strings and numeric serial date values, resulting in errors for the serial numbers when converted directly.
Identify the exact name of the column containing the mixed date formats and ensure your source text dates share a consistent delimiter, such as a slash or hyphen.
Add a Custom Column with M Code
Use a custom Power Query formula to check the format of each row and apply the correct conversion logic based on whether the value is text or a serial number.
Power Query can conditionally process row values. By checking if a cell contains a date delimiter (like a slash), we can leave text dates as they are, and convert the remaining numeric serial values into standard dates.
Launch Power BI Desktop or Excel, and click on 'Transform Data' to open the Power Query Editor where your dataset is loaded.
Navigate to the 'Add Column' tab on the top ribbon and click the 'Custom Column' button.
In the Custom Column dialog, enter a new name for your column. In the formula box, paste the following code: = if Text.Contains([ColumnName], "/") then [ColumnName] else Text.From(Date.From(Number.FromText([ColumnName])), "en-GB").
Ensure you replace 'ColumnName' in the formula with the exact name of your mixed-data column. Click 'OK' to apply.
Click the data type icon on the header of your newly created custom column and select 'Date' from the dropdown menu to finalize the conversion.
Handle Spreadsheets and Data Effortlessly with WPS Office
While Power Query is specific to the Microsoft ecosystem, you can handle most data cleanup, formatting, and analysis tasks easily with WPS Office. As a free and lightweight alternative, WPS Spreadsheet offers robust tools to fix inconsistent data directly.
- 1. Open Data in WPS Spreadsheet: Launch WPS Office and open your .xlsx or .csv dataset containing the mixed date formats.
- 2. Use Text to Columns: Highlight the problematic column, go to the 'Data' tab, and click 'Text to Columns' to quickly standardize the formatting.
- 3. Apply Date Formatting: Right-click the cleaned column, select 'Format Cells', and choose your preferred Date format.
Frequently Asked Questions
Why do Excel dates sometimes appear as 5-digit numbers in Power Query?
Excel stores dates as sequential serial numbers starting from January 1, 1900 (e.g., 44000 represents a date in 2020). When this data is imported into Power Query without a predefined date format, it defaults to showing these underlying numerical values.
Can I fix mixed dates without writing custom M code?
Yes, but it takes more steps. You can duplicate the column, convert one to 'Date' (which errors out the serial numbers) and the other to 'Whole Number' then 'Date' (which errors out the text dates), and finally merge them using a conditional column to pick the non-error values.
What if my text dates are in US format (mm/dd/yyyy)?
If your source dates are in US format, change the culture code in the custom column formula from 'en-GB' to 'en-US'. This ensures Power Query correctly interprets the month and day order when converting the text to a standard date.
