Power Query Problems

How to Combine Roles by Name and Keep Vacant Rows Separate in Power Query

Olivia Miller Oct 9, 2026 869 views

Question details

The user needs to consolidate a dataset by grouping individuals by name and combining their multiple roles into a single cell, while keeping "vacant" role rows untouched and separate.

How to Combine Roles by Name and Keep Vacant Rows Separate in Power Query

Product: Excel Power Query
Device & OS: not provided
Scenario: Consolidating a personnel or organizational dataset where some individuals hold multiple roles that need to be merged, but vacant positions must be preserved as distinct individual rows.
Observed behavior: The user wants a resulting table that features a consolidated Name column, retains original FormalName values, groups non-vacant entries with concatenated roles, and appends the vacant entries back at the end without merging them.

Before you start

Ensure your Excel data is formatted as an official Table and that you have identified the exact column headers for 'PreferredName', 'FormalName', and 'Role' before launching the Power Query Editor.

Solution 1Recommended

Group Non-Vacant Rows and Append Vacant Rows via Power Query

This solution involves separating the vacant and non-vacant rows into distinct datasets, combining the roles for the non-vacant rows using the Group By feature, and then appending the vacant rows back into the final table.

To properly handle the vacant rows without accidentally grouping them together, you must filter them out before applying the Text.Combine logic. By separating the logic, you ensure that every vacant position remains an independent row in your final dataset.

Load the Data into Power Query

Select your data table in Excel, navigate to the Data tab, and click 'From Table/Range'. This will load your data into the Power Query Editor. The native M code source step will typically look like: Source = Excel.CurrentWorkbook(){[Name="YourTableName"]}[Content].

Standardize the Name Column

Add a new Custom Column or use the 'Replace Values' feature to replace any null or empty values in the 'PreferredName' column with the values from the 'FormalName' column. Rename this new consolidated column to 'Name'.

Separate Vacant and Non-Vacant Rows

Filter the 'Name' or 'Role' column to exclude 'Vacant' entries. (Tip: You can duplicate your initial query first—one query filtered for Non-Vacant, and one filtered for Vacant rows, so you can easily append them later).

Group By Name and Combine Roles

In the Non-Vacant query, select the 'Name' and 'FormalName' columns. Click 'Group By' on the Transform tab. In the prompt, set the New Column Name to 'Role' and the Operation to 'Sum'. After closing the prompt, go to the formula bar and change the aggregation code from List.Sum([Role]) to Text.Combine([Role], ", ").

Append and Sort the Final Table

Go to the Home tab and click 'Append Queries'. Select the query containing your Vacant rows to add them back to your grouped Non-Vacant data. Finally, click the dropdown on the 'Name' or 'Role' column to sort the table as desired, and click 'Close & Load'.

Group Non-Vacant Rows and Append Vacant Rows via Power Query

Preserving FormalName: If the original 'FormalName' value must remain in your final output, do not remove it before grouping. Make sure to select it alongside the 'Name' column when performing the 'Group By' action.

Free Microsoft Office alternative

Experience seamless data management with WPS Office

While advanced M-code scripting like Power Query is specific to Microsoft Excel, WPS Office offers a powerful, free, and lightweight alternative for everyday spreadsheet management, data filtering, and Pivot Table analysis.

Completely free and lightweight Office suiteSeamless compatibility with Microsoft Excel formats (.xlsx, .xls, .csv)Built-in advanced Pivot Tables and data consolidation featuresFamiliar user interface requiring zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

How do I combine text from multiple rows in Power Query?

You can combine text by using the 'Group By' feature. Select the columns you want to group by, choose any aggregation (like Sum) temporarily, and then edit the generated M code in the formula bar to use Text.Combine([YourColumnToCombine], ", ").

Why did my FormalName column disappear after grouping?

When you use the 'Group By' function in Power Query, any column not specified as a grouping key or an aggregated output is automatically dropped. To keep the FormalName column, you must select it as one of the keys to group by.

Can I automatically replace null names with formal names in Power Query?

Yes. You can add a Conditional Column with the logic: If [PreferredName] equals null, then output [FormalName], otherwise output [PreferredName]. This easily standardizes missing name values.

Excel & Data Problems