Excel Power Query: Merge Rows from Columns or Tables

How-to basics · Updated November 22, 2026

To merge row data in Excel Power Query, you either combine values from multiple columns into one column per row (using the Merge Columns feature) or combine rows from two different tables into one table (using the Merge Queries join). For combining values across columns in the same row, select the columns, go to Add Column > Merge Columns, choose a separator, and rename the new column. For combining rows from separate tables, use Home > Merge Queries and pick a join kind like Left Outer or Full Outer.

What “Merge Row Data” Actually Means in Power Query

Power Query (built into Excel 2016 and later, and available as an add-in for 2010/2013) is a data transformation tool. “Merging row data” can mean two different things, and users often confuse them:

  • Merging columns within a row – e.g., combining First Name and Last Name into a single Full Name column. This is a column-wise operation, but the result is one value per row.
  • Merging rows from multiple tables – e.g., combining a Customers table and an Orders table into one table based on a matching key. This is a row-wise join, similar to a SQL JOIN.

Both are done in the Power Query Editor, not with Excel formulas. The editor opens by selecting a range or table, then Data > From Table/Range (or From Sheet in newer versions).

How to Merge Columns into One Row Value

Suppose you have columns A (First Name) and B (Last Name) in a sheet named “People”. Steps:

  1. Select the data and go to Data > From Table/Range. Power Query opens.
  2. Hold Ctrl and click the column headers for First Name and Last Name.
  3. Right-click a selected column header and choose Merge Columns (or go to Add Column > Merge Columns).
  4. In the dialog, choose a separator (Space, Comma, Custom, etc.) and type a new column name like “Full Name”.
  5. Click OK, then Home > Close & Load to return the merged data to Excel.

If you want to merge rows that are stacked vertically (e.g., multiple rows per ID) into one row, you need to group first. Use Home > Group By, then in the aggregation step choose “All Rows” (available in the Advanced option) to keep the sub-rows as a table, and then extract values with a custom column or by expanding the table.

How to Merge Rows from Two Different Tables

To combine rows from two queries (like Sales and Product info), use the Merge Queries feature:

  1. In the Power Query Editor, ensure both tables are loaded as queries (they appear in the left pane).
  2. Select the first query (e.g., “Orders”).
  3. Go to Home > Merge Queries. Choose the second query (e.g., “Products”).
  4. Select the matching columns in each table (e.g., ProductID).
  5. Choose a join kind: Left Outer (keep all rows from the first table), Inner (only matching rows), Full Outer (all rows from both), etc.
  6. Click OK. A new column appears with “Table” values. Click the expand icon (double arrow) on that column header, select which columns to include, and click OK.

This is the standard way to combine rows from two related tables. The result is a single flat table with rows from both sources aligned by the key.

Choosing the Right Join Kind for Merging Rows

The join kind determines which rows appear in the merged output:

  • Left Outer – all rows from the first table, plus matching rows from the second. Non-matching rows get nulls for the second table’s columns.
  • Right Outer – all rows from the second table, plus matching rows from the first.
  • Full Outer – all rows from both tables, with nulls where no match exists.
  • Inner – only rows that have a match in both tables.
  • Left Anti – rows only in the first table (no match). Useful for finding missing records.
  • Right Anti – rows only in the second table.

For most merges, Left Outer is the safe default because it preserves all rows from your primary table. If you need to see unmatched rows, use Anti joins. If you need a full list of all possible combinations, use Cross Join (available in the Merge dialog under “Join Kind” as “Full Outer” only if you select no columns? Actually, cross join is done by creating a custom column with a dummy key).

Common Problems and How to Fix Them

Several issues trip up users when merging row data:

  • Data types mismatch – If the key columns have different types (text vs. number), the merge won’t match. Change both to the same type using Home > Data Type before merging.
  • Duplicate keys – If a key appears multiple times in the second table, the merge will produce multiple rows for that key (a many-to-one or many-to-many join). Check for duplicates with Home > Remove Duplicates if you expect one-to-one.
  • Expanding tables creates too many columns – When you expand a merged column, you may get many columns. Use the “Select All Columns” checkbox carefully, or choose only the ones you need.
  • Merging columns with different separators – If you want a comma and space, type “, ” in the Custom separator field. If you want a line break, choose “Custom” and press Ctrl+J in the field (in Windows).
  • Performance issues – Merging large tables (millions of rows) can be slow. Reduce data before merging by filtering or removing unnecessary columns.

Power Query vs. Excel Formulas for Merging Row Data

You can merge column data in a worksheet with formulas like =A2 & " " & B2 or =TEXTJOIN(" ", TRUE, A2:C2). For merging rows from two tables, you could use VLOOKUP or INDEX/MATCH, but those are limited to pulling one column at a time and don’t handle many-to-many joins well. Power Query is superior for:

  • Combining multiple columns into one with custom separators and handling nulls.
  • Joining tables on multiple keys (select multiple columns in the merge dialog).
  • Refreshing data automatically when the source changes (set up a refresh in Data > Queries & Connections).
  • Performing complex transformations before merging (e.g., cleaning data, unpivoting).

For a one-off quick merge, a formula is fine. For repeatable, robust data processing, Power Query is the right tool.

Frequently Asked Questions

How do I merge rows with the same ID into one row in Power Query?
Use Home > Group By. Select the ID column, then in the aggregation step choose “All Rows” (in Advanced options). This creates a column of tables. Then add a custom column to extract the values you want, e.g., Table.Column([Grouped], "Value") or use Text.Combine to join them. Finally expand or extract as needed.

Can I merge rows from two different Excel sheets without a common column?
Yes, but you need a common key. If no key exists, you can add an index column to both tables (Add Column > Index Column) and merge on that. If you want a full cross product (every row from table A paired with every row from table B), create a dummy column with the same value in both tables and merge on that.

Why does my merged column show “Error” instead of the combined text?
This usually happens when one of the source columns has a data type that doesn’t support text concatenation, like a number stored as text with leading zeros. Change both columns to Text type before merging. Also check for null values – if a row has null in one column, the merge may return null unless you use a custom column with a conditional.

Is there a way to merge rows without opening the Power Query Editor?
No, the merge features are only available in the Power Query Editor. You can open it by selecting your data and pressing Data > From Table/Range. After merging, you can load the result back to a worksheet.

What is the difference between Merge and Append in Power Query?
Merge combines rows from two tables based on matching columns (like a join). Append stacks rows from two tables on top of each other (like putting two lists together). Use Append when you have the same columns in both tables and want to combine the rows vertically.

Can I merge rows in Power Query on a Mac?
Yes, Power Query is available in Excel for Microsoft 365 on Mac. The interface is similar, but some shortcuts differ. For example, to select multiple columns, hold Command instead of Ctrl. The merge and append features are the same.

Final Tips for Merging Row Data Successfully

Always check the data types of your key columns before merging. Use Home > Remove Duplicates if you need a unique key. After merging, review the expanded columns to avoid duplicate column names. Finally, name your queries clearly – it makes the merge dialog easier to navigate. If you’re new to Power Query, start with a small sample of your data to verify the result before applying to the full dataset.

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.