[Excel/Power Query] Error: #NULL! or Null Value Issues — How to Fix Missing or Empty Data Sources

Summary

Power Query may display #NULL! or blank values when source data, columns, or connections are missing. This issue usually arises after merges, column renames, or broken source links. To fix it, identify which query step introduces nulls, replace or filter them out, and ensure column names and data types match across sources. Works on Excel 2016+, Microsoft 365, and Power Query for Windows/Mac.

Context

Nulls in Power Query represent missing or undefined data. They often appear after structural changes in a source file (renamed columns, deleted sheets, or new headers), or when merges return unmatched rows. Unlike Excel’s #NULL! intersection error, Power Query’s null values mean “no data.” These nulls propagate through transformations — especially typed columns (Date, Number) — and can cause subsequent steps to fail with type or evaluation errors. Detecting the exact step introducing nulls is key to resolution.

Probable Cause

  • Renamed or missing columns. A field referenced in a later step no longer exists in the source.
  • Failed merge. Key mismatches produce unmatched rows filled with nulls.
  • Data type enforcement. Applying numeric or date types to null-filled columns triggers conversion errors.
  • Disconnected source. The original file, table, or range was moved or renamed, returning null or empty tables.

Quick Fix

  1. Open Power Query Editor. Data → Get Data → Launch Power Query Editor.
  2. Locate the failing step. In the Applied Steps pane, click each step until you see nulls or an error preview.
  3. If columns were renamed: edit the step manually or rename columns to match the new source structure.
  4. Replace null values. Use the formula bar or GUI to substitute placeholders.
= Table.ReplaceValue(Source, null, "N/A", Replacer.ReplaceValue, {"ColumnName"})
  1. Check source connectivity. Right-click the query name in the Queries pane → Refresh Preview → verify the connection works.

Full Example

Scenario 1: Merge created a column full of nulls.

Expression.Error: The key didn't match any rows in the table.

Cause: The join keys use different names or data types (e.g., text vs number).

Fix: Align both sides before merging.

= Table.NestedJoin(
    TableA, {"ID"},
    Table.TransformColumnTypes(TableB, {{"ID", type text}}),
    {"ID"},
    "NewColumn"
)

Scenario 2: Column renamed or deleted in the source.

#"Changed Type" step fails with:
Expression.Error: The column 'CustomerName' of the table wasn't found.

Fix: Rename the column or update the previous step:

= Table.RenameColumns(Source, {{"ClientName", "CustomerName"}})

Scenario 3: Null values in a numeric column cause conversion errors.

DataFormat.Error: We couldn't convert to Number.

Fix: Replace nulls before applying Changed Type.

= Table.ReplaceValue(Source, null, 0, Replacer.ReplaceValue, {"Amount"})

Pitfalls & Debug

  • Symptom → All values in a merged column are null. Fix → Check join key names and data types on both tables.
  • Symptom → “Column not found” after refreshing source. Fix → Update renamed fields in Applied Steps.
  • Symptom → Nulls appear after expanding nested tables. Fix → Verify that each row actually contains nested data before expansion.
  • Symptom → Entire table returns null after source update. Fix → Confirm file path, sheet name, or query connection.
  • Symptom → Errors after applying types. Fix → Replace nulls with default values before type conversion.

Validation & Next Steps

Use Power Query tools to trace and validate nulls:

= Table.Profile(#"Previous Step")  # Displays counts, nulls, and data types

Preview each transformation step to locate where nulls first appear. Replace or filter nulls early to maintain stable data pipelines. Consider adding conditional columns:

= Table.AddColumn(Source, "CleanedColumn", each if [Column] = null then "N/A" else [Column])

Finally, refresh all queries after fixing sources to confirm that data loads correctly without null propagation.

Sources

https://support.microsoft.com/en-us/office/power-query-overview

https://learn.microsoft.com/en-us/powerquery-m/table-replacevalue

https://learn.microsoft.com/en-us/powerquery-m/table-profile

Labels

Tool/Power Query, OS/Cross-platform, Topic/Data Import & Null Handling