Troubleshooting Refresh Errors and Data Type Mismatches

What “Refresh Errors” and “Data Type Mismatches” Really Mean

In Power Query, a refresh error is any condition that prevents a query from completing successfully when it re-runs against the source. Some errors are hard failures (the query stops), while others are row-level issues that surface as error values inside specific cells. Data type mismatches are a common root cause: a step expects a value to be a number/date/text/etc., but the incoming value cannot be interpreted that way. The result can be a failed refresh, an error column, or (more dangerous) silently wrong results if a type is inferred incorrectly.

Troubleshooting is easier when you separate problems into three categories: (1) source access and connectivity problems, (2) schema/shape problems (missing columns, renamed headers, changed file structure), and (3) value/type problems (unexpected characters, mixed formats, locale differences). This chapter focuses on systematically diagnosing refresh failures and preventing type mismatches from breaking repeatable data prep.

Recognizing the Most Common Error Patterns

1) “We couldn’t authenticate” / “Access denied” / “The credentials provided are invalid”

These are source access errors. They often appear after password changes, moving files to a new location, switching from local to SharePoint, or refreshing on a different machine/account. The query logic may be fine; the refresh fails before transformations run.

2) “The column ‘X’ of the table wasn’t found”

This is a schema error. A later step references a column name that no longer exists (renamed, removed, or promoted headers changed). It can also happen when combining files and one file is missing a column.

3) “DataFormat.Error: We couldn’t parse the input provided as a Date/Number”

This is a value/type mismatch. A column typed as Date contains values like 2025/13/01, N/A, or 01-02-03 (ambiguous). A numeric column may contain currency symbols, commas, or text notes.

Continue in our app.

You can listen to the audiobook with the screen off, receive a free certificate for this course, and also have access to 5,000 other free online courses.

Or continue reading below...

Download the app

4) “Expression.Error: The key didn’t match any rows in the table”

This often occurs when a step tries to navigate to a specific item (a sheet name, table name, or file) that no longer exists. It is common with Excel workbooks when sheet names change.

5) “Formula.Firewall” or privacy-related blocks

These are refresh blockers caused by combining sources under privacy constraints. While privacy levels and refresh strategy design are covered elsewhere, you still need to recognize this pattern during troubleshooting because it looks like a query logic failure but is actually a security boundary issue.

A Practical Troubleshooting Workflow (Repeatable and Fast)

Use a consistent workflow so you don’t guess. The goal is to isolate the failing step, identify the failing row/value (if applicable), and then decide whether to fix the data, harden the query, or both.

Step 1: Reproduce the error in Power Query Editor

• Open the query in Power Query Editor.
• Click Refresh Preview (or refresh the query) to reproduce the error.
• Read the full error message. Copy it into a note so you don’t lose details while clicking around.

Step 2: Locate the failing step in Applied Steps

Refresh errors usually point to a specific step. If the UI highlights a step, start there. If not, click steps from top to bottom until the preview breaks. The first step that produces an error is your starting point.

Tip: Many errors appear “late” but are caused “early.” For example, a type conversion step fails because an earlier step introduced unexpected text (like concatenation or replacement). Still, the first failing step is the best place to begin.

Step 3: Determine whether it’s a query-level failure or row-level errors

• Query-level failure: the preview doesn’t load at all, or the step returns a single error instead of a table.
• Row-level errors: the table loads but some cells show Error. You can filter the column to only errors.

Step 4: Inspect the data at the boundary where it breaks

Once you find the failing step, inspect the step immediately before it. Look for:

• Unexpected column names (extra spaces, different casing, duplicates).
• Mixed types in a column (numbers and text).
• Locale-specific formatting (decimal comma vs decimal point, day/month order).
• Hidden characters (non-breaking spaces, tabs, line feeds) in text that should be numeric.

Step 5: Choose a fix strategy

In general, you have three options:

• Fix the source data: best when you control the upstream process and can enforce standards.
• Harden the query: best when you cannot control upstream data and must tolerate variability.
• Both: enforce what you can, and still guard against surprises.

Diagnosing and Fixing Data Type Mismatches

Why type mismatches happen in Power Query

Power Query uses types to decide how to interpret values and how to perform operations. Mismatches commonly happen when:

• A column contains mixed content (e.g., numbers plus “TBD”).
• Dates arrive in multiple formats (e.g., 2026-01-13 and 13/01/2026).
• Numeric values include symbols or separators (e.g., $1,200, 1 200, 1.200,50).
• Type inference changes between refreshes because the first N rows look different (Power Query samples data).
• Nulls and blanks appear where a conversion step expects a value.

Step-by-step: Find the exact values causing conversion errors

When a type conversion step fails, do this:

• Click the step that converts types (often named Changed Type).
• If the table loads with error cells, filter the affected column to Errors.
• If the entire step fails (no table), click the step just before it, then temporarily add a diagnostic column to test conversion with try ... otherwise.

Example diagnostic approach for a numeric column named Amount:

let    Source = ... ,    BeforeType = Source,    AddTest = Table.AddColumn(        BeforeType,        "Amount_Test",        each try Number.From([Amount]) otherwise null,        type number    ),    AddErrorFlag = Table.AddColumn(        AddTest,        "Amount_IsError",        each [Amount] <> null and [Amount_Test] = null,        type logical    )in    AddErrorFlag

Now filter Amount_IsError to true to see the problematic raw values. This is faster than guessing and lets you decide whether to clean, replace, or exclude those rows.

Hardening conversions with try/otherwise

Instead of letting a conversion step break refresh, you can convert safely and decide what to do with failures. Common patterns:

• Convert and keep null on failure: preserves refresh and highlights missing/invalid values.
• Convert and keep original text on failure: useful when you want an “exceptions” report.
• Convert and route failures to a separate query: best for operational monitoring.

Example: safe date conversion with a fallback to null:

Table.TransformColumns(    PreviousStep,    {        {"OrderDate", each try Date.From(_) otherwise null, type date}    })

Handling locale-specific numbers and dates

Locale issues show up when the file author uses a different regional format than your system or the refresh environment. A value like 1.234,56 can be interpreted incorrectly if the conversion assumes a different decimal separator.

Use locale-aware conversions when needed. For example, converting text to number using a specific culture:

Table.TransformColumns(    PreviousStep,    {        {"AmountText", each try Number.FromText(_, "de-DE") otherwise null, type number}    })

Similarly, for dates that arrive as text, you may need Date.FromText with a culture, or you may need to normalize the text first (e.g., replace separators) before conversion.

Preventing type inference surprises

Type inference can change if the first rows in a refresh differ from prior refreshes. To reduce surprises:

• Apply explicit types after you have stabilized column names and basic shaping.
• Avoid relying on automatic Changed Type steps generated early in the query if upstream variability is expected.
• When combining files, ensure the sample file is representative, or replace auto-generated type steps with explicit, hardened conversions.

Troubleshooting Schema Changes (Missing/Renamed Columns)

Typical symptoms

• A step referencing a column fails: “The column ‘X’ of the table wasn’t found.”
• Expanding a nested table fails because the column list changed.
• Promoted headers behave differently because the first row changed.

Step-by-step: Identify where the schema changed

• Click the step just before the failure and inspect the column list in the preview.
• Compare expected column names to actual (watch for trailing spaces and punctuation).
• If the query combines multiple files, isolate a single problematic file by filtering the file list (or temporarily point to one file) and refresh preview.

Hardening against missing columns

When you cannot guarantee that every refresh has every column, you can add missing columns with nulls before downstream steps reference them. This keeps the schema stable.

Example: ensure required columns exist:

let    Required = {"Customer", "OrderDate", "Amount"},    AddMissing = List.Accumulate(        Required,        PreviousStep,        (state, col) => if Table.HasColumns(state, col) then state else Table.AddColumn(state, col, each null)    )in    AddMissing

After this, downstream steps can safely reference those columns. You still need to decide how to handle rows where the column is null because it was missing in the source.

Dealing with renamed columns

If column names change (e.g., Order Date becomes OrderDate), you can implement a rename mapping that only renames columns that exist. This avoids errors when one of the names is absent.

let    RenamePairs = {        {"Order Date", "OrderDate"},        {"Amt", "Amount"}    },    ExistingPairs = List.Select(        RenamePairs,        each Table.HasColumns(PreviousStep, _{0})    ),    Renamed = Table.RenameColumns(PreviousStep, ExistingPairs, MissingField.Ignore)in    Renamed

This pattern is especially useful when you receive files from multiple teams that use slightly different headers.

Troubleshooting Navigation Errors (Sheets, Tables, and Files Not Found)

Typical symptoms

• “Expression.Error: The key didn’t match any rows in the table.”
• Errors when selecting a sheet/table by name.

Step-by-step: Confirm what exists at refresh time

• Go to the step where you navigate into the workbook (often after Excel.Workbook).
• Inspect the list of items (Name, Kind, Hidden).
• Check whether the expected sheet/table name exists and whether its Kind matches (Sheet vs Table).

Hardening navigation by selecting dynamically

Instead of hard-coding a single name, you can select the first matching item by criteria (e.g., Kind = “Table” and Name contains “Sales”). If no match exists, you can return an empty table (or raise a controlled error with a clearer message).

let    Items = Excel.Workbook(File.Contents(PathToFile), null, true),    Candidates = Table.SelectRows(Items, each [Kind] = "Table" and Text.Contains([Name], "Sales")),    Pick = if Table.RowCount(Candidates) > 0 then Candidates{0}[Data] else #table({}, {})in    Pick

This reduces refresh failures when the exact name changes but a recognizable pattern remains.

Working with Error Rows: Keep Refreshing, Still See the Problems

A robust approach is to keep the main query refreshable while still surfacing data issues for correction. Instead of letting errors stop the refresh, you can:

• Convert with try/otherwise and store nulls for invalid values.
• Add an “Issue” column describing what failed.
• Split exceptions into a separate query for review.

Step-by-step: Create an exceptions table from conversion failures

Assume you have a table where OrderDate and Amount arrive as text. You want a clean table plus an exceptions table.

1) In the main query, add safe conversions and flags:

let    Source = ...,    AddAmount = Table.AddColumn(Source, "Amount_Num", each try Number.FromText([Amount]) otherwise null, type number),    AddDate = Table.AddColumn(AddAmount, "OrderDate_Date", each try Date.FromText([OrderDate]) otherwise null, type date),    AddIssue = Table.AddColumn(        AddDate,        "Issue",        each Text.Combine(            List.RemoveNulls({                if [Amount] <> null and [Amount_Num] = null then "Invalid Amount" else null,                if [OrderDate] <> null and [OrderDate_Date] = null then "Invalid OrderDate" else null            }),            "; "        ),        type text    )in    AddIssue

2) Create a reference query (not a duplicate) to capture exceptions:

let    Ref = MainQueryName,    Exceptions = Table.SelectRows(Ref, each [Issue] <> null and [Issue] <> "")in    Exceptions

3) In the main query, filter out exception rows (optional) or keep them with null converted values depending on reporting needs.

This pattern keeps refresh stable and gives you a clear operational list of what needs fixing upstream.

Interpreting “Error” vs “Null” vs “Blank” During Troubleshooting

Power Query distinguishes between different “missingness” states, and confusing them can lead to incorrect fixes:

• Error: a failed evaluation (e.g., conversion failed, missing field). Errors can stop refresh or appear in cells.
• null: an explicit absence of value. Conversions can produce null intentionally (e.g., try ... otherwise null).
• Blank string (""): text value of length zero. It is not null and may fail numeric/date conversion unless handled.

When troubleshooting, check whether the problematic values are truly null or are blank strings/spaces. A column that “looks empty” may actually contain spaces or non-printing characters.

Common “Gotchas” That Cause Refresh Failures

Hidden whitespace and non-printing characters

Values like " 123", "123 ", or non-breaking spaces can break conversions or joins. When you see unexpected conversion failures, inspect the raw text length or apply trimming/cleaning before conversion.

Duplicate column names after expansions or merges

Expanding nested tables can create duplicate column names, which may cause later steps to reference the wrong column or fail. If you see odd behavior after an expand step, check the column list for duplicates and rename immediately after expansion.

Ambiguous dates

Text like 01/02/2026 can mean January 2 or February 1 depending on culture. If your refresh environment changes (desktop vs service, different regional settings), the same text can convert differently. Normalize date formats or use culture-specific parsing consistently.

Mixed numeric formats in one column

A single column might contain 1200, 1,200, 1.200, and 1.200,50 across files. Decide on a standard and normalize the text before conversion, or branch logic based on detected patterns.

A Minimal Checklist for Faster Debugging

• Find the first failing step in Applied Steps.
• Classify: access, schema, navigation, or value/type.
• If it’s type-related, isolate failing values using filters or a try diagnostic column.
• Stabilize schema: add missing columns, conditional renames, and safer expansions.
• Use locale-aware parsing when formats vary by region.
• Prefer refreshable queries with exceptions tables over brittle “all-or-nothing” conversions.