Power Query operates as a strict, type-safe data engine. When you encounter a DataFormat.Error or see localized “Error” values populating individual cells within your preview grid, it indicates a fundamental mismatch between the physical nature of your raw data and the structural rules of your query. Unlike Expression.Error logic breaks or severed connection paths, a formatting error means the engine successfully loaded the data, but cannot safely convert it into the requested type. This hub serves as your categorical diagnostic map, designed to help you identify the specific behavioral pattern of your data type failure so you can route the issue to the exact cleaning protocol.
The Most Common Variations
Data format failures manifest in highly specific patterns based on the type of data being parsed. Identifying whether the mismatch is rooted in regional calendars, strict mathematical constraints, or complex file encoding is the first step in resolving the issue. Review the symptom groupings below to find the pattern that matches your query’s behavior.
Date, Time, and Regional Locale Misalignments
This is arguably the most common and destructive formatting error. The symptom involves date columns returning localized DataFormat.Error warnings, or worse, quietly swapping months and days without alerting you. This occurs because Power Query attempts to parse international dates (e.g., DD/MM/YYYY) using the local system’s default regional settings (e.g., MM/DD/YYYY).
- Most Often Linked To: International CSV exports, foreign CRM platforms, and strict
DateTimeZoneAPIs. - Typical Risk Level: Critical
- See Detailed Guide:
Numeric, Null, and Integer Formatting Clashes
In this variation, the engine expects pure mathematical inputs but receives contaminated strings or out-of-bound values. Symptoms include DataFormat.Error appearing when a column formatted as a Whole Number receives a currency symbol ($), a scientific notation string (1.23E+10), or a value that physically exceeds the storage limits of a 64-bit integer. It also encompasses math operations failing because they attempted to calculate against a null value.
- Most Often Linked To: Financial software exports, scientific IDs imported as numbers, and blank database fields.
- Typical Risk Level: Moderate
- See Detailed Guide:
Data Profiling, Inference, and Truncation Limits
Power Query attempts to be helpful by automatically profiling the first 200 rows of your data to guess the data type. If row 1 is a number, it sets the column to “Whole Number.” If row 10,000 contains a letter, the query crashes upon load. The symptoms here are queries that look perfect in the preview window but fail entirely when loaded to the Excel grid.
- Most Often Linked To: Automated “Changed Type” steps, messy human-entered Excel files, and massive text strings.
- Typical Risk Level: High
- See Detailed Guide:
Structural Encoding and Syntax Failures
This variation occurs when data is ingested in a highly structured payload, like JSON, XML, or Base64, but the text itself violates the strict syntax rules of that format. Symptoms include an immediate DataFormat.Error when attempting to parse a JSON document, or unreadable characters appearing in text columns due to character encoding mismatches.
- Most Often Linked To: API web payloads, legacy ANSI text files, and corrupted XML namespaces.
- Typical Risk Level: Moderate
- See Detailed Guide:
Folder Appends and Structural Clashes
When automating the combination of dozens of CSV or Excel files from a folder, slight deviations in the source files will destroy the append operation. Symptoms include new columns appearing with “Column1” headers, or entire columns returning nulls because one file named the column “Revenue” and another named it “Sales.”
- Most Often Linked To: “Combine Files” operations, shifting CSV headers, and mismatched vendor templates.
- Typical Risk Level: High
- See Detailed Guide:
Interface Management and Step Auditing
The final category encompasses how users interact with the Power Query interface to handle errors. Symptom behaviors include queries that silently drop data because the “Replace Errors” function was used indiscriminately, or hardcoded column transformations that break the moment a source file changes format.
- Most Often Linked To: Indiscriminate error replacement, automated UI steps, and missing auditing protocols.
- Typical Risk Level: Low
- See Detailed Guide:
Factors That Increase Concern
Data formatting errors are heavily influenced by the upstream data environment and the volume of records processed. A query built on a perfectly clean, 500-row SQL table carries very little risk. However, ingesting 500,000 rows of human-entered data from a shared Excel workbook introduces massive variance. Because Power Query evaluates data types strictly, a single space entered into a numeric column by an end-user will break the downstream data model. Furthermore, relying on Power Query’s default behavior of profiling only the “Top 200 Rows” creates a ticking time bomb for large datasets, where anomalies deep within the file remain undetected until the final refresh cycle.
Symptom Comparison
| Variation | Most Likely Cause | Urgency Level |
|---|---|---|
| Date/Locale Mismatch | Importing European DMY dates into a US MDY system. | Critical |
| Data Profiling Limits | PQ guessed the data type based on row 1, but row 5,000 contains text. | High |
| Folder Appends | Appending monthly CSVs where the column header names changed slightly. | High |
| Encoding/Syntax | Parsing JSON strings with missing brackets or reading ANSI as UTF-8. | Moderate |
| Numeric Contamination | Currency symbols or scientific notation text inside whole number columns. | Moderate |
Time and Cost Expectations
The complexity of repairing a DataFormat.Error depends entirely on whether you can enforce rules upstream. If you can force your CRM to export strict data types, the Power Query fix is trivial. If you must accept heavily contaminated, unstructured data from external vendors, the complexity skyrockets. Writing M-Code to dynamically hunt for “dirty” values, handle shifting column names during folder appends, and sanitize non-printing characters requires advanced transformation logic and significant processing time, slowing down the entire refresh pipeline.
Hard-Stop Signals
If you observe the following conditions during data cleaning, halt standard transformations immediately. These are thresholds indicating severe data integrity risks:
- Silent Data Loss: You use the “Remove Errors” UI button on a massive dataset, and downstream totals suddenly drop by 20% without warning because entire rows were discarded.
- Inverted Chronology: A locale mismatch causes October 12th (10/12/YYYY) to silently load as December 10th (12/10/YYYY) without triggering a hard error, completely invalidating time-series reporting.
- The Int64 Override: An integer overflow error appears on a primary key column, meaning database IDs are being fundamentally truncated, destroying all relationship links in your Power Pivot model.
Connected Symptoms
If your query is failing but the symptom points beyond localized cell errors or data typing, your pipeline may be suffering from structural M-Code breaks or secure connection severances. Broaden your forensic scope by consulting these adjacent diagnostic hubs: