Excel Power Query: Combine Columns Without Losing Data

How-to basics · Updated November 18, 2026

In Excel for Microsoft 365 on Windows, concatenate columns in Power Query by selecting them in the Power Query Editor and choosing Transform > Merge Columns, then choosing a separator. To keep the original columns, use Add Column > Custom Column and enter an M expression such as Text.Combine({Text.From([First Name]), Text.From([Last Name])}, " ").

What concatenating columns means in Power Query

Concatenating means joining values from two or more columns into one text value for each row. If a table has separate First Name and Last Name columns, for example, you can create a Full Name value such as “Mina Patel.” The operation is row by row: it joins fields from the same record, not every value in one column with every value in another.

Power Query is Excel’s data-import and transformation tool. It records steps such as removing columns, changing data types, and combining text. When the source changes, you can refresh the query to apply those steps again. That makes it useful when you regularly import files or need a repeatable cleanup process, rather than a one-time worksheet formula.

There are two common approaches:

  • Merge Columns: a ribbon command for combining selected columns into one result, with a chosen separator.
  • Custom Column: an M expression that creates a new column, giving you more control and preserving the inputs.

Prepare the source table first

Start with a rectangular data range whose first row contains clear column headers. In Excel, you can select the range and use Insert > Table, or press Ctrl+T on Windows. Confirm that the table has headers if Excel has not detected them. A well-formed table makes it easier for Power Query to identify the data and include additional rows added later.

To open the query editor, select a cell in the table and choose Data > From Table/Range. Excel opens the Power Query Editor, where the preview displays the table’s columns and rows. Check that the columns you plan to combine have the expected names and types. If a column contains numbers, dates, or mixed values, decide whether the combined result should use a particular text format before joining it.

These steps describe Excel for Microsoft 365 on Windows. Power Query authoring options and ribbon labels can differ on Mac, and the set of available features is not identical in Excel for the web. If you do not see the same command, check that you are working in the desktop Power Query Editor.

Use Merge Columns from the ribbon

Merge Columns is the quickest choice when you want a single combined field and do not need a custom rule for each row. In the Power Query Editor:

  1. Select the columns to combine. Click one header, then hold Ctrl while clicking other headers on Windows. Select them in the order you want their values to appear.
  2. Choose Transform > Merge Columns. The Add Column tab also offers a Merge Columns command.
  3. In the dialog, choose a separator, such as a space, comma, or dash. Choose a custom separator if the listed choices do not fit the data.
  4. Enter a name for the resulting column and confirm. Review the preview to check that the values and separator look right.
  5. When the query is ready, choose Home > Close & Load to return the result to Excel.

For example, selecting City and State and choosing a comma-space separator can produce “Portland, OR.” The order of the selected columns matters, so select City before State. Merge Columns combines the chosen fields into one output; if you need to keep the original fields in the final result, duplicate them before merging or use a custom column instead.

Create a custom column to preserve the originals

A custom column is usually the safer option when you want the combined value alongside the source fields, or when you need to handle blanks and formatting deliberately. In the editor, choose Add Column > Custom Column, give the new field a name, and enter an M expression. For headers First Name and Last Name, this expression joins the values with a space:

Text.Combine({Text.From([First Name]), Text.From([Last Name])}, " ")

In this example, Text.From converts each field to text, the braces make a list of the two values, and Text.Combine joins that list using a space. The expression is Power Query M, not an Excel worksheet formula. The new column appears in the preview while the original columns remain available. Select the result’s data type as Text if Power Query has not assigned it automatically.

You can add more fields to the list. For example, to join a street, city, and postal code, add each field in the desired order and use a suitable separator. Think about punctuation and spacing before combining: a space works for names, while a comma and space may suit a city and region. A single separator is applied between the list’s non-null text values, so a more complicated address format may be clearer as multiple custom columns or conditional logic.

Choose separators and control formatting

The separator is part of the output, not just a visual display. No separator is appropriate for some identifiers or codes, a space is common for names, and a hyphen can make a product code easier to read. Use a comma carefully: if the output may be exported as comma-separated text, the comma can be interpreted as a field delimiter by another program.

Power Query combines values, not their Excel cell appearance. A date that looks like “2 Nov 2026” in a worksheet may be stored as a date value, and a number displayed with leading zeroes may be stored without them. Converting those values to text can therefore produce a result that differs from the visible cell format. For predictable output, explicitly format dates with Date.ToText and choose the format and culture you require; for numbers, decide how decimals, grouping marks, and leading zeroes should be represented before joining them.

Also distinguish a true text value from formatting applied in Excel. If an ID is stored as the number 27 but displayed as 0027 using a cell format, a text conversion may yield “27,” not “0027.” If the zeroes are meaningful, create or retain them as text before the concatenation step, or apply a deliberate text-formatting transformation in the query.

Handle nulls, empty strings, and data types

A missing value and an empty string are not always the same thing. A null represents no value; an empty string is text with zero characters. This distinction can affect separators and the appearance of the result. The Text.Combine example above ignores null items in its list, so a row with one missing name does not automatically display the word “null.” An empty string, however, is still text and may leave a separator that looks unnecessary.

For example, if the first name is “Mina” and the last name is null, the Text.Combine expression produces “Mina,” without adding a visible word for the missing part. But if the last-name field contains an empty string, the list includes that text value; the separator may still be placed between the list entries. Inspect rows with incomplete data in the preview rather than assuming every row follows the complete-name pattern.

If unwanted punctuation or spaces appear, one option is to clean the source columns first by trimming whitespace and replacing empty strings with nulls. Another is to build a conditional custom expression that adds a separator only when both values are present. This is useful for addresses, where a blank apartment number should not leave an extra comma. Before applying a cleanup rule, decide whether a missing field should be omitted, represented by a placeholder, or treated as a data-quality issue.

Check column types as well. The concatenated result is text, even if the source columns contain numbers or dates. That is often right for labels and IDs, but it is not suitable if you intend to perform arithmetic on the combined field. Keep numeric values in separate columns for calculations, and use the combined text only as a display or export field.

Refresh the query and load the result

Power Query stores the combine operation as a step in the query. After using Home > Close & Load, the output is typically loaded to a worksheet table. If the source Excel table changes, use Data > Refresh All or right-click the query output and choose Refresh to rerun the transformation. When new rows are added to the source table, they can be included in the refresh.

A refresh recalculates the query result; it does not turn the output into a manually maintained worksheet formula. Changes typed directly into the loaded output may be overwritten on the next refresh. Make source corrections in the original table or in the query steps, depending on where the data comes from. If the result is going to a particular worksheet location, review the load options before closing the editor.

If refresh fails, check whether a source column was renamed, deleted, or changed to an incompatible type. Query steps refer to column names, so a renamed header can break a later step. The Applied Steps pane helps locate where the query first encounters a problem; selecting earlier steps can show whether the issue began before or during the concatenation.

Merge Columns versus worksheet formulas

Power Query is a good fit for repeatable imports and transformations: combine the data once in the query, then refresh it as the source changes. A worksheet formula is more suitable when you want the result to update immediately as someone edits cells in the sheet, or when the data is already maintained as a normal worksheet range. The formula approach and Power Query are separate tools; a query result is not itself a live cell formula.

Within Power Query, Merge Columns is convenient for straightforward combinations, while a custom column is preferable when you need to preserve source fields, convert values, or handle missing data. If you are trying to combine rows from separate tables based on a matching key, that is a different operation: use Merge Queries, not Merge Columns. Merge Queries joins tables by matching columns; Merge Columns joins values across fields in the same row.

Frequently Asked Questions

Does Merge Columns delete the original columns?
In the query result, the selected fields are combined into one column rather than kept as separate output fields. If you need the originals, duplicate them before using Merge Columns or add a custom column instead.

Can I concatenate more than two columns?
Yes. Select multiple headers for Merge Columns, or include more fields in the list passed to Text.Combine in a custom column. Select the fields in the order they should appear and check the separator in the preview.

How do I combine columns without a space between them?
In the Merge Columns dialog, choose no separator. In a custom column, use an empty text separator in Text.Combine. This is common for codes, but check that the joined values remain unambiguous.

Why did my date or number change when I combined it?
Power Query uses the underlying value and its conversion to text, not necessarily the worksheet’s display formatting. Convert dates or numbers to text with an intentional format before combining when the exact appearance matters.

Is Merge Columns the same as Merge Queries?
No. Merge Columns concatenates fields from the same row into one value. Merge Queries joins two tables using matching key columns, much like looking up related records. Use the command that matches whether you are combining fields or linking tables.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.