Duplicate and combine columns
Keep raw values for auditing while producing derived columns.
~8 min read · beginner · Module 4: Working with columns
By Tahir Nas · Examples run in our simulator · Updated
Before this: Rename and reorder columns
Many transformations replace a column's values in place: trimming, changing case, replacing codes. Sometimes you need the original too, for example to show reviewers what the source said, or to merge on the raw value and display the clean one. Duplicating the column first gives you both.
Duplicate, then transform the copy
let
Source = #table({"SKU", "Name"}, {{"k-01 ", "kite"}, {" r-02", "reel"}}),
Copied = Table.DuplicateColumn(Source, "SKU", "SKU (raw)"),
Cleaned = Table.TransformColumns(Copied, {{"SKU", each Text.Upper(Text.Trim(_)), type text}})
in
CleanedResult
| SKU | Name | SKU (raw) |
|---|---|---|
| K-01 | kite | k-01 |
| R-02 | reel | r-02 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Combine columns into one
The opposite job is joining several columns into one text column, such as a display name or a composite key. Merge Columns in the editor writes Table.CombineColumns with a combiner function, and removes the source columns:
let
Source = #table({"Store", "Aisle", "Bin"}, {{"LDS", "04", "B"}, {"YRK", "11", "A"}}),
Combined = Table.CombineColumns(Source, {"Store", "Aisle", "Bin"}, Combiner.CombineTextByDelimiter("-", QuoteStyle.None), "Location")
in
CombinedResult
| Location |
|---|
| LDS-04-B |
| YRK-11-A |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
If you need to keep the parts, add a custom column instead: Table.AddColumn(Source, "Location", each Text.Combine({[Store], [Aisle], [Bin]}, "-"), type text). Text.Combine works only on text, so convert numbers with Text.From first.
Combined keys for merges
A combined column is a common workaround for a two-column join key. Power Query can merge on several columns directly (see the composite key lesson), which is clearer and avoids accidental matches such as "A1" & "0" equalling "A" & "10". If you do combine, always use a delimiter that can't appear in the values.
- Duplicate when you need the raw value later in the query or in the output.
- Combine with Merge Columns when the parts aren't needed any more.
- Use a custom column with Text.Combine when you want both the parts and the combined value.
Where to find this in Excel
- Excel for Windows
- Select two or more text columns > Transform > Merge Columns
- What you see
- A dialog asks for a separator and a new column name; the originals are replaced by the merged column.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Merge columns (Power Query) (Microsoft Support).
Common mistakes
- Combining parts without a delimiter, so different part pairs produce the same key.
- Combining a number column directly; convert it to text first or the step fails with a type error.
- Duplicating a column after it has already been cleaned, so the 'raw' copy isn't raw.
Excel, Power BI and the simulator
- Excel and Power BI Desktop: right-click a header → Duplicate Column; select several columns → Transform → Merge Columns (replace) or Add Column → Merge Columns (keep the originals).
- Merge Columns offers built-in separators (comma, tab, space…) and a custom one; all map to Combiner.CombineTextByDelimiter.
- When a part is null, the simulator's combiner skips it; this behaviour is listed for native verification on the compatibility page (KD10), so avoid relying on it.
Table.DuplicateColumnTable.CombineColumnsCombiner.CombineTextByDelimiterNext step
Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.
Take the Working with columns quiz