Power Query M — Common Errors & Fixes

A troubleshooting reference for the errors you'll encounter most often when

adapting these templates to your own data.

Error: "Expression.Error: The column 'X' of the table wasn't found."

Cause: You referenced a column name that doesn't exist in your data.

Common triggers:

• Source renamed a column (e.g., "Revenue" → "Total Revenue")
• Typo in column name (case-sensitive!)
• Column was removed in an earlier step

Fix:

// Check actual column names first:
Table.ColumnNames(YourTable)

// Use try...otherwise for optional columns:
try YourTable[MaybeColumn] otherwise null

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

Cause: A cell that should be numeric contains text (e.g., "N/A", "-", "TBD").

Fix:

// Replace non-numeric sentinels BEFORE type conversion:
Cleaned = Table.ReplaceValue(Source, "N/A", null, Replacer.ReplaceValue, {"Amount"}),
Typed = Table.TransformColumnTypes(Cleaned, {{"Amount", type number}})

// Or use try per-row:
Table.AddColumn(Source, "SafeAmount", each
    try Number.From([RawAmount]) otherwise null, type nullable number)

Error: "Formula.Firewall: Query references other queries or steps..."

Cause: Power Query's privacy firewall is blocking a cross-source operation

(e.g., using a parameter from one source to filter another).

Fix (development):

• Excel: File → Options → Query Options → Privacy → "Always ignore Privacy Level settings"
• Power BI: File → Options → Global → Privacy → "Always ignore"

Fix (production): Set both sources to "Organizational" privacy level in Data Source Settings.

Error: "DataSource.Error: Could not find file 'C:\path\file.csv'."

Cause: Hardcoded file path doesn't exist on this machine (common when

sharing workbooks between team members).

Fix: Use a parameter for the base path:

// Define parameter "BasePath" = "C:\Users\YourName\Data\"
// Then reference it:
Source = Csv.Document(File.Contents(BasePath & "filename.csv"))

Error: "Expression.Error: There weren't enough elements in the enumeration..."

Cause: You tried to access a row by index (e.g., Table{0}) but the table is empty.

Fix:

// Use try...otherwise:
FirstRow = try Source{0} otherwise null

// Or check first:
if Table.RowCount(Source) > 0 then Source{0} else null

Error: "Expression.Error: We cannot apply operator & to types Number and Text."

Cause: Concatenation (&) only works on text values. One side is a number.

Fix:

// Convert to text explicitly:
Combined = Text.From([Year]) & "-" & Text.From([Month])

Error: "Key didn't match any rows in the table" (during join)

Cause: The lookup value doesn't exist in the target table. This isn't always

an error — it may just be a missing reference.

Fix:

// Use LeftOuter join (keeps all rows from left table, nulls for no-match):
Table.NestedJoin(Source, {"Key"}, Lookup, {"Key"}, "Matched", JoinKind.LeftOuter)

// Then handle nulls in the expanded columns

Query Runs Forever (No Error, Just Slow)

Common causes:

1. Broken query folding — Check by right-clicking a step → "View Native Query".

If grayed out, folding broke. Move non-foldable steps to the end.

2. Cartesian join — You joined two tables without proper keys, creating

an N×M explosion. Verify join columns have matching values.

3. List.Contains on unbuffered list — Buffer the list first:

   BufferedIDs = List.Buffer(LookupTable[ID])

4. Recursive function without depth limit — Add a max-depth parameter.

"This step results in an error for some rows"

Cause: Some rows have values that can't be processed by a transform

(e.g., date parsing fails on "Unknown").

Diagnosis:

// Keep error rows for inspection:
Errors = Table.SelectRowsWithErrors(Source, {"ProblematicColumn"})

Fix: Clean the data BEFORE the failing step, or use try:

Table.AddColumn(Source, "SafeDate", each
    try Date.FromText([DateString]) otherwise null)

Parameter Refresh Issues in Power BI Service

Symptom: Query works in Desktop but fails after publishing to Service.

Common causes:

• File path parameters (Service can't access local drives)
• Dynamic data sources (Gateway doesn't recognize parameterized URLs)
• Privacy level conflicts in the cloud

Fix:

• Use Web URLs or SharePoint paths instead of local file paths
• Ensure dynamic URLs are marked as "Organizational" privacy
• Configure Gateway data source to match the parameterized connection

Best Practices to Avoid Errors

1. Always specify data types explicitly — Don't rely on auto-detection

2. Use try...otherwise for any operation that might fail per-row

3. Buffer lists before using them in List.Contains

4. Keep foldable operations at the top of your query

5. Use Table.ColumnNames to verify schema before referencing columns

6. Test with a small sample (Table.FirstN(Source, 100)) during development

Part of Data Analyst Toolkit

Power Query M — Function Quick Reference

The most useful M functions organized by category, with signatures and one-line descriptions.

This covers functions used throughout the 58 templates in this collection.

Table Functions

Text Functions

Number Functions

Date & Time Functions

List Functions

Record Functions

Type System

Operators

Part of Data Analyst Toolkit