2.2.3. Resolving Data Import Errors
💡 First Principle: Power Query errors come in two scopes: cell-level errors, where a specific value can't be converted or computed, and step-level errors, where a whole step can't run (a column it names is missing, a file can't be found). Cell-level errors are fixed by handling values; step-level errors are fixed by repairing the query.
Cell-level errors usually come from data type conversion: the text "N/A" in a column changed to Whole Number, or a date in an unexpected format. Column quality shows the Error percentage, and you have three handling options:
| Action | What it does | When to use |
|---|---|---|
| Keep Errors | Keeps only rows with errors in the selected columns | Investigate: isolate the bad rows to see what's wrong |
| Remove Errors | Deletes entire rows that contain an error in the selected columns | The rows are genuinely invalid and safe to drop |
| Replace Errors | Substitutes a value (null, 0, "Unknown") and keeps the row | The row is valid; only one value is bad |
A sound workflow: duplicate or reference the query, use Keep Errors to see the offending values, fix the root cause (such as Change Type > Using Locale for dates in dd/mm/yyyy format, or replacing "N/A" with null before the type change), and only then decide whether any residual errors should be removed or replaced. Clicking a cell's Error link shows the message, for example DataFormat.Error: We couldn't convert to Number.
Step-level errors stop the query. Classic causes:
- Renamed or missing source columns: Expression.Error: The column 'Sales Amount' of the table wasn't found. The automatic Changed Type step hard-codes column names, so a source rename breaks it. Fix the step (or the source), and consider removing unnecessary auto type steps.
- Moved or renamed files: DataSource.NotFound. Use Data source settings > Change Source, or a file-path parameter (2.1.4).
- Credential or permission failures: update credentials in Data source settings (Desktop) or the semantic model settings (service).
- Privacy combination: Formula.Firewall (2.1.2).
In the service, a failed scheduled refresh reports the error in the refresh history; the same diagnostic logic applies, plus gateway-specific causes covered in 5.1.4.
⚠️ Exam Trap: Remove Errors deletes the whole row, not just the bad value, which changes row counts and totals silently. If the requirement says "retain all transactions," the answer is Replace Errors (or fixing the type conversion), not Remove Errors.
Reflection Question: After a source system upgrade, refresh fails with "The column 'Cust_ID' of the table wasn't found." Which applied step most likely contains the hard-coded name, and what two fixes are available depending on whether the rename was intentional?
