[Excel/Power Query] Error: DataFormat.Error — Invalid cell value or cannot convert to Date (date parsing issue) — How to Fix It

Summary

DataFormat.Error: Invalid cell value or We couldn’t convert to Date occurs when Power Query fails to interpret a text value as a valid date. This happens when the text format doesn’t match your locale settings (e.g., 13/08/2024 misread as month/day), or when the column contains non-date values like N/A or —. The fix is to apply the correct locale during type conversion (via “Using Locale”) or use Date.FromText with a culture parameter. Invalid or mixed-format rows should be handled using try … otherwise null.

Context

Power Query reads text dates based on your system locale or the file’s metadata. If the input file (CSV, Excel, TXT) uses a different regional format—such as dd/MM/yyyy for Europe or MM/dd/yyyy for U.S.—direct type conversion to Date will fail. Common symptoms include red error cells or only part of the column converting successfully. Locale mismatches are particularly frequent when importing CSVs from other regions or from online data sources with fixed date formatting. The issue also occurs if the column mixes date strings with text placeholders or numeric serials.

Probable Cause

  • Locale mismatch: The date text format differs from your Power Query locale.
  • Mixed formats: The column mixes numeric serials (Excel dates) and text strings.
  • Invalid placeholders: Values like N/A, #VALUE!, or dashes appear among dates.
  • Blank or malformed text: Empty or invalid date patterns such as 13/25/2024.
  • Automatic type detection: Power Query infers the wrong type when regional formats conflict.

Quick Fix

  1. Identify the error source. In the Power Query Editor, find the step showing DataFormat.Error. Columns with red icons indicate conversion problems.
  2. Manually set locale on conversion: Right-click the column → Change Type → Using Locale → Date, then choose the correct region (e.g., English (United States) or Portuguese (Portugal)).
  3. Replace invalid text entries. Convert N/A or other non-date values to null before conversion.
# Replace placeholders and apply locale-aware conversion Cleaned = Table.ReplaceValue(Source, "N/A", null, Replacer.ReplaceValue, {"Date"}); Converted = Table.TransformColumnTypes(Cleaned, {{"Date", type date}}, "en-GB") # dd/MM/yyyy
  1. Convert dynamically using M functions. If formats vary, use conditional or “try” logic:
# Safe conversion handling mixed formats SafeDate = Table.AddColumn(Source, "DateFixed", each try Date.FromText([Date]) otherwise null, type date)
  1. When importing from CSV: Use locale detection explicitly in the import function:
# Load CSV with explicit locale Source = Csv.Document(File.Contents("C:\data.csv"), [Delimiter=",", Columns=5, Encoding=1252, Locale="en-GB"]);
  1. Filter or inspect invalid rows. Add a diagnostic column to locate problematic entries:
# Identify rows that fail date conversion Errors = Table.AddColumn(Source, "ErrorCheck", each if try Date.FromText([Date]) is null then [Date] else null)

Full Example

Scenario: You import a CSV with European-style dates (“dd/MM/yyyy”) but your Power Query locale is set to U.S. (“MM/dd/yyyy”). Values like “12/08/2024” succeed, but “13/08/2024” trigger errors.

# Initial load Source = Csv.Document(File.Contents("C:\sales.csv"), [Delimiter=",", Columns=3, Encoding=1252, Locale="en-US"]); Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars=true]);
Wrong conversion: locale mismatch

ErrorStep = Table.TransformColumnTypes(Promoted, {{"Date", type date}}); # Fails on dd/MM/yyyy

Fix: specify locale explicitly

Fixed = Table.TransformColumnTypes(Promoted, {{"Date", type date}}, "en-GB"); # Works correctly

Optional: handle text placeholders

Cleaned = Table.ReplaceValue(Fixed, "—", null, Replacer.ReplaceValue, {"Date"});

Alternate dynamic method:

# Mixed source with various formats Dynamic = Table.AddColumn(Promoted, "ParsedDate", each let d = [DateText] in if Text.Contains(d, "/") then try Date.FromText(d, "en-GB") otherwise null else if Text.Contains(d, "-") then try Date.FromText(d, "en-US") otherwise null else null, type date)

Numeric Excel serials: If you see numbers like 45567, convert with Date.From():

# Convert Excel serial to date WithDates = Table.AddColumn(Source, "ConvertedDate", each if Value.Is([Date], type number) then Date.From([Date]) else null)

Pitfalls & Debug

  • Symptom → Dates parse inconsistently between months. Fix → Locale mismatch. Force en-GB or en-US explicitly.
  • Symptom → DataFormat.Error only for certain rows. Fix → Filter invalid entries (like N/A) before conversion.
  • Symptom → Some values become null silently. Fix → Use try … otherwise and log failed conversions for review.
  • Symptom → Error after file refresh. Fix → Source file exported with different region; reapply locale or dynamic parsing.
  • Symptom → Dates appear swapped (e.g., 08/12 vs 12/08). Fix → Confirm whether Power Query used system or file locale; adjust “Using Locale” conversion accordingly.

Validation & Next Steps

To validate conversions and catch mismatches early:

# Add type check Check = Table.AddColumn(Fixed, "IsDate", each Value.Is([Date], type date))
Filter invalid results

InvalidRows = Table.SelectRows(Check, each [IsDate] = false)

Once validated, propagate locale-aware transformations upstream in all import queries. Standardize the date pattern across files (ISO: yyyy-MM-dd) when possible to eliminate ambiguity. Consider wrapping date parsing in reusable M functions for consistent handling across datasets.

Sources

Date.FromText (Power Query M)

Table.TransformColumnTypes

Handling DataFormat.Error

Labels

Tool/Power Query, OS/Cross-platform, Topic/Date & Locale Conversion