Expression.Error: The index is outside the bounds of the array.

In Power Query, Expression.Error: The index is outside the bounds of the array means your M-code attempted to access an item in a list or array at a numerical position that does not exist. This most often occurs when indexing into a zero-row result, looking past the end of a list, or forgetting that Power Query uses zero-based indexing.

Quick Answer

Check the formula bar for positional index operators like {0}, {1}, or functions like List.Select(...){0}. Verify the length of the underlying list or table. In Power Query, the first item is index 0, not 1. If your source dataset is empty or filtered to zero rows, requesting {0} triggers this error because no items exist.

---

Understanding Zero-Based Indexing and Positional Access

Power Query uses zero-based numbering for lists and rows. The first item in any sequence sits at index 0, the second at index 1, and the $N\text{th}$ item sits at index $N-1$.

When you write:

FirstRow = Source{0}

Power Query retrieves the first record of the table Source. If Source contains zero rows, or if you write {1} on a table that contains exactly one row, the engine searches beyond the boundary of the allocated collection and throws:

Expression.Error: The index is outside the bounds of the array.

This error is common in automated workflows where the volume of incoming source data fluctuates between refreshes.

---

Common Root Causes and How to Resolve Them

1. Zero Rows Returned After Filtering

A typical Power Query pattern isolates a specific row by filtering a table and extracting the first matching item:

• Faulty Implementation: #"Filtered Rows" = Table.SelectRows(Source, each [TransactionID] = "TX-9999"), TargetRecord = #"Filtered Rows"{0} If TX-9999 is missing from the incoming data, #"Filtered Rows" evaluates to an empty table (0 rows). Calling {0} immediately fails.
• Safe Alternative using try ... otherwise: TargetRecord = try #"Filtered Rows"{0} otherwise null This returns a manageable null instead of stopping query execution, allowing downstream steps to branch or handle the missing data cleanly.

2. Off-by-One Errors in List Traversal

When manually writing loops or list extractions, counting items starting at 1 causes an out-of-bounds error on the final element.

• Faulty Implementation:
  If a list MyList contains 5 items: LastItem = MyList{5} // Fails: Valid indexes are 0, 1, 2, 3, 4
• Correct Implementation:
  Use List.Last or calculate the zero-based boundary dynamically: LastItem = List.Last(MyList) // Or: LastItem = MyList{List.Count(MyList) - 1}

3. Accessing Unsplit Text Columns

When using Text.Split or Table.SplitColumn, expecting a fixed number of segments causes failures whenever a row contains fewer delimiters than expected:

• Example Scenario:
  Splitting full names into First and Last using space delimiters: #"Extracted Last" = Table.AddColumn(Source, "LastName", each Text.Split([FullName], " "){1}) If a row contains a single-word name (e.g., “Cher”), Text.Split yields a list with only one item (index 0). Attempting to read index {1} crashes the query.
• Safe Implementation:
  Use List.Skip or check the list length before indexing: #"Extracted Last" = Table.AddColumn(Source, "LastName", each let Parts = Text.Split([FullName], " ") in if List.Count(Parts) > 1 then Parts{1} else null )

---

Diagnostic Step-by-Step Procedure

1. Click on the failing step in the Applied Steps pane on the right side of the screen.
2. In the formula bar, look for curly braces {} containing an integer: e.g., {0}, {5}.
3. Click on the step directly preceding the error in the Applied Steps pane.
4. Check the row count at the bottom-left corner of the Power Query window:
   • Does it say 0 rows?
   • If you are indexing a list, click the cell to inspect the list length.
5. If the list or table is empty, verify whether an earlier filter step removed all records unexpectedly.
6. If an empty list is a normal business condition (such as zero pending orders), replace the raw {0} indexer with try ... otherwise or check Using try…otherwise in M-code to create custom error handling steps for structural defensive patterns.