skip to content

In Power Query, what happens when a Changed Type step hits an unconvertible value?

level: middleimportance: should knowfreq 55%

answer

  1. one bad cell need not fail the whole table
  2. types were guessed from a sample, not the whole file
  3. the auto step hardcodes column names
  4. try, replace, remove, keep — pick deliberately
  5. 03/04 means two different days

basics

~20 s

That single cell becomes an Error value rather than failing the query; surrounding rows load normally. A step-level failure is different — a missing or renamed column aborts the whole query. Handle cell errors explicitly with try/otherwise, Replace Errors or Remove Errors.

solid answer

~50 s

Power Query distinguishes **cell-level** from **step-level** errors. A `Table.TransformColumnTypes` step that meets `"N/A"` in a column typed as number produces a `DataFormat.Error` in that one cell; the rest of the table is unaffected, and Power BI Desktop reports afterwards that N rows had errors, with those cells arriving empty in the model. A step-level error — `The column 'Amount' of the table wasn't found` after a source rename — aborts the whole query, because the auto-generated Changed Type step hardcodes column names. The trap is that the Changed Type step Power Query adds on import guesses types from a sample of the leading rows, so a stray text value further down the file errors only later, often only in the Service. Handle it deliberately: `try … otherwise`, Replace Errors with a default, or Keep Errors into a quarantine query you can inspect. And set culture explicitly on date conversions so `03/04/2024` cannot mean two different days on two different machines.

code

powerquery · 6 lines
powerquery
// culture pinned, so 03/04/2024 cannot flip meaning between machines
Table.TransformColumnTypes(
    Source,
    {{"OrderDate", type date}, {"Amount", type number}},
    "en-GB"
)

go deeper

for a junior

Recognise the Error value in a cell, know the Changed Type step is where most of them come from, and know that replacing or removing errors is a decision, not a formality.

for a middle

Distinguish cell-level from step-level errors, explain that type detection sampled only leading rows, and use try/otherwise plus an explicit culture on conversions.

for a senior

Design coercion so failures are visible: clean sentinels first, quarantine rejects to a loaded table, and never let a silently emptied cell reach a total that somebody reports to a board.

for a principal

Treat recurring type errors as a missing source contract — decide whether the fix belongs upstream with the producing system rather than being re-patched in every report that reads the file.

## Two kinds of error, with very different blast radius Power Query has an `error` value that lives *inside a cell*, alongside numbers, text and dates. When `Table.TransformColumnTypes` tries to turn `"N/A"` into a number, the result is not a crash: that one cell holds `DataFormat.Error: We couldn't convert to Number`, and every other cell in the table is untouched. The query still produces a table. A **step-level** error is different. If a step cannot even be evaluated — the classic being `Expression.Error: The column 'Amount' of the table wasn't found` after someone renamed a column at the source — the whole query fails and nothing loads. The reason column renames break so often is the auto-generated step itself. When you connect to a file or table, Power Query typically inserts a `Changed Type` step as one of the first Applied Steps, and that step lists column names literally. Rename `Amount` to `NetAmount` upstream and the step, not just the model, breaks. ## Where the wrong type came from Type detection on import is a *guess based on a sample of the leading rows of the source*, not a full scan. A column that holds numbers for thousands of rows and then `"N/A"`, `"-"` or `"1,234"` in row 50,000 will be typed as number, and the offending rows error at refresh — not necessarily on the day you built the report. This is why a report that worked for a month suddenly shows errors after a new file arrives. The usual disciplined response is to remove the automatic Changed Type step, do your cleaning first (trim, replace the sentinel values, remove the thousands separators), and set types deliberately at the end as a step you own. ## Locale is a silent corrupter Date and decimal parsing depend on culture. `03/04/2024` is 3 April in one locale and 4 March in another; `1.234` is one and a bit in one and one thousand two hundred thirty-four in another. `Table.TransformColumnTypes` takes an optional third argument, the culture: ``` Table.TransformColumnTypes(Source, {{"OrderDate", type date}}, "en-GB") ``` Without it, parsing follows the file's locale setting, and the machine that performs the refresh may differ from the machine where you authored — a gateway host with different regional settings will parse the same file differently. The dangerous failure here is not an error at all: dates parse *successfully* into the wrong day, and nothing warns you. Pin the culture explicitly on any date or decimal conversion from text. ## Handling errors on purpose M's error-handling operator is `try … otherwise`, usable in a custom column: ``` Table.AddColumn(Source, "AmountNum", each try Number.FromText([Amount]) otherwise null) ``` The UI offers three error transforms, and the choice between them is a data-governance decision, not a formatting one: - **Replace Errors** substitutes a value — reasonable when the sentinel genuinely means "unknown", provided the replacement is null and not zero. Replacing with zero turns a missing amount into a real amount and quietly wrongs every total. - **Remove Errors** (`Table.RemoveRowsWithErrors`) drops the offending rows. It makes the refresh clean and silently reduces your data — the most dangerous of the three when applied reflexively. - **Keep Errors** returns only the erroring rows, which is a diagnostic tool: build a duplicate query that keeps errors and load it to the model as a small data-quality table so somebody sees the rejects instead of losing them. ## What loading does with remaining errors If you load a table that still contains cell errors, Power BI Desktop surfaces a warning telling you a number of rows had errors and offers to show them; those cells arrive in the model empty. Because the behaviour and the exact reporting differ between Desktop and a scheduled refresh in the Service, and because a value silently arriving empty is a wrong number waiting to happen, the professional position is that errors should never reach the load — they should be replaced, quarantined or fixed upstream before that point. ## Prevention beats handling Most type errors are symptoms of a source contract that was never stated: a column that is "a number, except when it says N/A". Where you control the source, fix it there. Where you do not, make the coercion explicit and visible — clean the sentinels, set the type with a culture, route the rejects to a quarantine query — so that when the file format shifts, the report tells you rather than quietly shrinking.

  • Why does a report that refreshed fine for months suddenly fail with a column-not-found error?
    Because the auto-generated Changed Type step names columns literally. A rename at the source — or a supplier shipping a file with a slightly different header — makes that step reference a column that no longer exists, and a step-level error aborts the whole query rather than erroring one cell. Removing or rewriting the auto step, or normalising headers first, is the fix.
  • Is Remove Errors an acceptable way to make a refresh succeed?
    Only when you know exactly which rows it drops and have decided they are worthless. Used reflexively it hides shrinking data behind a green refresh: the report gets quietly smaller while every visual still looks healthy. Prefer replacing with null, or keeping the errors in a small quarantine query loaded to the model so somebody is accountable for them.
  • Why can the same file parse into different dates on two machines?
    Text-to-date conversion follows a culture. If `Table.TransformColumnTypes` has no culture argument, parsing depends on the locale in effect where the refresh runs — and an on-premises gateway host may have different regional settings than the author's laptop. Nothing errors; the days are simply wrong. Always pass the culture explicitly.

saying these in an interview costs you the question

  • Thinks any conversion error fails the entire refresh
  • Replaces errors with zero instead of null in a numeric column
  • Trusts automatic type detection to have read the whole file
  • Uses Remove Errors to make a refresh green without checking what was dropped
  • Ignores locale when converting text to dates

context

resolving links…