Power QueryExercises

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
    Cleaned

Result

SKUNameSKU (raw)
K-01kitek-01
R-02reel r-02

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The duplicate is added at the end. The original column keeps its name and position, so later steps that use SKU are unaffected.

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
    Combined

Result

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.
Functions in this lesson:Table.DuplicateColumnTable.CombineColumnsCombiner.CombineTextByDelimiter

Next step

Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.

Take the Working with columns quiz