'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
Fix DataFormat.Error When Power Query Cannot Parse a Date in Excel
Question details
The user encounters a DataFormat.Error in Power Query because dates imported from a CSV file fail to parse due to mismatched regional formats.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Importing data from a CSV file into Power Query where the date formatting varies from the system's default regional settings.
- Observed behavior
- Power Query displays a DataFormat.Error and fails to convert the imported text string into a valid date column.
Open the original CSV file in a standard text editor like Notepad to verify the exact format of the dates (e.g., DD/MM/YYYY vs. MM/DD/YYYY) before attempting to change the locale in Power Query.
Change Data Type Using Locale in Power Query
Use the 'Using Locale' feature to force Power Query to interpret the date string according to the specific region's format.
Power Query relies on your system's regional settings to interpret dates by default. When you import a CSV generated in a different region, the date sequence (like Day/Month vs. Month/Day) conflicts with your settings, resulting in a parsing error. Specifying the locale at the column level resolves this.
Load your CSV data into Excel. If you see the DataFormat.Error, click on 'Transform Data' to open the Power Query Editor.
Locate the column that contains the date errors. Right-click on the column header to open the context menu.
In the right-click menu, hover over 'Change Type' and select 'Using Locale...' at the bottom of the list.
In the dialog box, set the Data Type to 'Date'. Then, click the Locale dropdown and choose the region that exactly matches the format of your source data (for example, select 'English (United Kingdom)' if your dates are formatted as DD/MM/YYYY). Click OK.
Handle Your CSV Data Effortlessly with WPS Office
If you are struggling with complex Excel add-ins and Power Query errors, try WPS Office. It provides a lightweight, highly compatible spreadsheet tool that makes importing and formatting CSV data straightforward and error-free.
- 1. Open WPS Spreadsheet: Launch WPS Spreadsheet and navigate to the Data tab on the top ribbon.
- 2. Import Your CSV File: Click on 'Import Data' and select your CSV file from your local drive.
- 3. Format Dates in the Import Wizard: Use the Text Import Wizard to specify your delimiter. In the final step, select the date column, choose 'Date', and set the correct sequence (e.g., DMY) to import without errors.
Frequently Asked Questions
Why does Power Query show a DataFormat.Error for dates?
This error occurs when the date format in your source data (like a CSV file) does not match the regional settings or default locale of your Power Query environment, making it unable to parse the text into a valid date.
Can I change the default locale for the entire Power Query workbook?
Yes, you can change the workbook's default locale by going to File > Options and settings > Query Options > Current Workbook > Regional Settings, and selecting the desired default locale.
What if the CSV has multiple date formats in the same column?
If a single column contains mixed date formats, you may need to split the column by delimiter, extract the day, month, and year components manually, and then reconstruct a standardized date column using a custom column formula.
Will changing the locale affect other columns?
No. Using the 'Change Type > Using Locale' option on a specific column only applies the parsing rule to that selected column. Your other columns and overall workbook settings remain unaffected.
