Analistable

How to merge columns in Power Query (Excel and Power BI)

By the Analistable team · Updated · 5 min read

Select the columns in the order you want them joined, then choose Transform → Merge Columns to replace them, or Add Column → Merge Columns to keep the originals. Pick a separator, name the new column and click OK. For more control, especially to skip blank values without leaving double separators, add a custom column with Text.Combine({[First], [Last]}, " ").

Part of our guide: How to merge data in Power BI, Power Query and Tableau

Merge Columns is not Merge Queries

The names are similar but the jobs are different. Merge Columns joins several columns of the same row into one text column, like CONCAT or TEXTJOIN in a worksheet. Merge Queries joins two tables on a key, like a lookup; that is covered in merging tables in Power BI. This page is about columns.

Using the Merge Columns command

  1. In the Power Query editor, hold Ctrl and click the columns in the order you want them joined. The click order, not the column order, decides the result.
  2. To replace the columns, choose Transform → Merge Columns. To keep the originals and add a new one, choose Add Column → Merge Columns.
  3. Choose a separator: none, colon, comma, equals sign, semicolon, space, tab, or custom (for example “ - ” or “|”).
  4. Type a name for the new column and click OK.
Merging Street, Town and Postcode with a comma and space
StreetTownPostcodeMerged
12 High StLeedsLS1 4AB12 High St, Leeds, LS1 4AB
3 Mill LaneYorkYO1 7HT3 Mill Lane, York, YO1 7HT
8 Park RdnullHG1 2RSsee below

Merge Columns works on text. If a selected column is a number or date, Power Query converts it to text in the generated step, using its default format. A date may come out as “14/03/2026” or “3/14/2026” depending on your locale, so format dates yourself first if the exact text matters.

What the generated step looks like

The command writes a Table.CombineColumns step. You can edit the separator or the new column name in the formula bar:

= Table.CombineColumns(
    Table.TransformColumnTypes(Source, {{"Postcode", type text}}, "en-GB"),
    {"Street", "Town", "Postcode"},
    Combiner.CombineTextByDelimiter(", ", QuoteStyle.None),
    "Address")

Blank and null values are where results differ between methods. With some versions and settings you can end up with a doubled separator, such as “8 Park Rd, , HG1 2RS”, when Town is empty. Check a row with a missing value before relying on the output. If doubled separators appear, use the custom column method below.

Text.Combine in a custom column

Add Column → Custom Column with Text.Combine gives you full control. Text.Combine ignores null items in the list, so missing parts don't leave gaps:

// Address without empty parts
Text.Combine({[Street], [Town], [Postcode]}, ", ")

// Treat empty strings as missing too
Text.Combine(List.Select({[Street], [Town], [Postcode]}, each _ <> null and Text.Trim(_) <> ""), ", ")

// Numbers and dates: convert explicitly
Text.Combine({[Region], Text.From([Year]), Date.ToText([Date], "yyyy-MM-dd")}, "-")

Empty strings are not nulls: an exported blank cell is often "". The second formula filters both out. The third shows explicit conversion, which avoids locale surprises and errors from mixing text and numbers.

Same row, two methods
StreetTownPostcodeText.Combine result
8 Park RdnullHG1 2RS8 Park Rd, HG1 2RS

Building a key for a merge

A common reason to merge columns is to create a composite key, for example Region and Product, so two tables can be joined on one column. You don't strictly need to: Merge Queries accepts several key columns at once. But a single key helps when you also want to check duplicates or use the key in Power BI relationships, which link on one column. Use a separator that cannot appear in the data, such as “|”, so “AB” + “C” and “A” + “BC” stay different.

Merge columns in Power BI with DAX instead

In Power BI you can also join columns after loading, in a calculated column. COMBINEVALUES("|", Sales[Region], Sales[Product]) joins with a delimiter, and the & operator works too: Sales[Region] & " - " & Sales[Product]. Doing it in Power Query is usually better for keys and labels because the column is created once at refresh and compressed like any other column. DAX columns are convenient for quick labels on an existing model.

For the worksheet equivalents (ampersand, CONCAT, TEXTJOIN) see combining two columns in Excel. In Analistable, joined labels are written in SQL, for example region || ' - ' || product or concat_ws(', ', street, town, postcode) in DuckDB, which also skips nulls. More options are in the Power BI, Power Query and Tableau guide.

Splitting a merged column back

If you merge columns and later need the parts again, Transform → Split Column → By Delimiter reverses it, as long as the separator never appears inside the values. That is another reason to choose an unusual separator such as | for keys. Addresses are the classic trap: a street such as “Flat 2, 10 High St” already contains a comma, so splitting on “, ” gives four parts instead of three.

Merged values and a safe separator
RegionProductMerged with |Split back
NorthKettle, 1.7LNorth|Kettle, 1.7LNorth / Kettle, 1.7L
SouthToasterSouth|ToasterSouth / Toaster

Where possible, keep the original columns (use Add Column rather than Transform) so you never need to split. Merged text is for display and keys; the separate columns are what you filter and group on.

Another frequent need is a readable period label, such as “2026-Q1”, built from a date. Merge Columns cannot format the date for you, so use a custom column: Text.From(Date.Year([Date])) & "-Q" & Text.From(Date.QuarterOfYear([Date])). The & operator joins text in M just as it does in a worksheet, but unlike Text.Combine it returns null if either side is null, so use it only on columns that are always filled.

Frequently asked questions

What is the difference between Merge Columns and Merge Queries?
Merge Columns joins several columns in one row into a single text column. Merge Queries joins two tables on matching key values.
How do I merge columns without losing the originals?
Use Add Column → Merge Columns instead of Transform → Merge Columns. The Add Column version keeps the source columns.
How do I stop double separators when a value is blank?
Use a custom column with Text.Combine, which ignores nulls, and filter out empty strings with List.Select if your blanks are empty text.

Related guides