Expression.Error: Field ‘X’ already exists in the record.

In Power Query, Expression.Error: Field 'X' already exists in the record occurs when a transformation step attempts to add, rename, or expand a column or field using a name that is already present in the current row record or table schema. Power Query strictly enforces unique column and field identifiers within any single table or record structure.

Quick Answer

Check the formula bar of the broken step. You are trying to create or rename a column using a name that already exists in your table. If using Add Column, give the new column a unique name. If using Table.ExpandTableColumn (merging or expanding tables), uncheck or rename overlapping columns, or ensure Use original column name as prefix is checked.

---

Understanding Field Collisions in Power Query Records

In M-code, every row of a table is internally treated as a Record. A record is an unordered collection of field-value pairs, structured like this:

[ CustomerID = 101, CustomerName = "Acme Corp", Total = 500.00 ]

Because field names serve as the unique keys used to look up values within a record, you cannot have two fields sharing the same name in the same record. Attempting to execute an operation that would produce:

[ CustomerID = 101, Total = 500.00, Total = 250.00 ] // ILLEGAL

instantly raises:

Expression.Error: Field 'Total' already exists in the record.

---

Common Triggers and How to Resolve Them

1. Expanding a Merged Table with Duplicate Column Names

This error occurs most frequently after a Merge Queries operation when expanding nested tables.

When you merge Query A and Query B, Power Query creates a new column containing nested tables. If you click the Expand icon on that column header, you are asked which columns to include.

• The Problem: If both Query A and Query B have a column named Status, and you uncheck Use original column name as prefix, Power Query tries to insert a second Status column directly alongside your existing Status column.
• The Solution:
  1. Click the gear icon next to the Expanded Table step in the Applied Steps pane.
  2. Check the box at the bottom: Use original column name as prefix. This will name the incoming column TableB.Status, avoiding any collision.
  3. Alternatively, uncheck the duplicate column if you do not need it from the second table.

2. Renaming a Column to an Existing Column Name

If your table contains columns named Sales and Total Sales, and you attempt to rename Total Sales to Sales, Power Query throws this error.

• Broken Code:
  If Sales is already an existing header, the step fails. #"Renamed Columns" = Table.RenameColumns(Source, {{"Total Sales", "Sales"}})
• Fix: Either rename the existing Sales column first (e.g., to Legacy Sales) or choose a unique name for the new column (e.g., Net Sales).

3. Creating a Custom Column with an Existing Name

When creating a custom column via Add Column > Custom Column, Power Query defaults the new column name to Custom.

If you rename that new column to match an existing header in your table, or if you create two consecutive custom columns without updating the default Custom name, the second step attempts to add a duplicate field.

• Always verify that the name in the New column name box at the top of the Custom Column dialog does not already exist in the Available columns list on the right.

4. Pivoting Data on Duplicate Attribute Names

When using Table.Pivot, the values inside your pivot column become the new column headers for the table.

• If your pivot column contains text values that match existing static column headers in the table, the pivoting engine attempts to create a duplicate column name.
• Fix: Before pivoting, inspect the unique values in your pivot column. Rename any existing static columns in your table that share names with the upcoming pivoted categories.

---

Quick Diagnostic Flowchart

When the error strikes:

1. Note the field name ‘X’ cited in the error message.
2. Scroll horizontally through your table preview at the step immediately preceding the error.
3. Search for column ‘X’.
4. Once you find it, examine the failing step:
   • If Expanding: Enable prefixes or uncheck column ‘X’.
   • If Renaming: Choose an alternate name.
   • If Adding a Column: Change the target column name in the formula bar.

If you are joining two complex datasets and need to review join behavior and duplicate key consequences, consult Expression.Error: Multiple matches found in a Join (Cartesian product trap).