Power Query Problems

Split Employee Benefits into Dynamic Columns with Excel Power Query

Maira MehtabMaira Mehtab Nov 18, 2026 869 views

Question details

Create one row per employee with a dynamic set of columns for each benefit record.

Split Employee Benefits into Dynamic Columns with Excel Power Query
Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
An employee benefits table contains multiple rows per employee, and the number of benefits may change over time.
Observed behavior
The desired output has one row per employee, with indexed columns for each benefit's date, plan, coverage level, employee cost, and employer cost.
Before you start

Confirm that each benefit is on a separate row and that every row has an employee ID. The example uses an Excel table named Benefits with columns named Employee ID, Benefit Date, Plan, Coverage Level, Employee Cost, and Employer Cost.

Solution 1Recommended

Group, index, and pivot benefit records in Power Query

Use this query to create numbered benefit columns that expand when employees have more benefit records.

The query groups records by employee ID, numbers the records within each group, and pivots each indexed field into a separate column. Column names combine the benefit number and field name, such as Benefit 1 - Plan.

Benefit numbering depends on the order of records within each group. If a benefit must retain the same number across refreshes, sort each group by a stable, unique benefit identifier before adding the index.

1
Create the source table

In Excel, select the benefit data and choose Insert > Table. On the Table Design tab, set the table name to Benefits. Match the six column names listed above or adjust them in the query.

2
Open Power Query

Select a cell in the Benefits table and choose Data > From Table/Range. In Power Query Editor, choose Home > Advanced Editor.

3
Add the grouping and pivot query

Replace the Advanced Editor contents with the following code, then select Done: let Source = Excel.CurrentWorkbook(){[Name="Benefits"]}[Content], Fields = {"Benefit Date", "Plan", "Coverage Level", "Employee Cost", "Employer Cost"}, Grouped = Table.Group(Source, {"Employee ID"}, { {"Rows", each Table.AddIndexColumn(Table.SelectColumns(_, Fields), "BenefitIndex", 1, 1, Int64.Type), type table} }), Expanded = Table.ExpandTableColumn(Grouped, "Rows", List.Combine({Fields, {"BenefitIndex"}})), Unpivoted = Table.UnpivotOtherColumns(Expanded, {"Employee ID", "BenefitIndex"}, "Field", "Value"), Named = Table.AddColumn(Unpivoted, "ColumnName", each "Benefit " & Text.From([BenefitIndex]) & " - " & [Field], type text), PivotInput = Table.SelectColumns(Named, {"Employee ID", "ColumnName", "Value"}), Result = Table.Pivot(PivotInput, List.Distinct(PivotInput[ColumnName]), "ColumnName", "Value", List.First) in Result

4
Load and refresh the result

Choose Home > Close & Load to place the result in Excel. After changing the source table, use Data > Refresh All and check the resulting columns and data types.

Group, index, and pivot benefit records in Power Query
Plan for changing columns: Unpivoting omits null values, so a field that is blank for every record may not appear as a column. Employees whose benefit fields are all blank will also be absent; merge the result with a separate employee list if those employees must remain. Downstream queries or formulas that reference specific column names may need adjustment after refresh.
Free Microsoft Office alternative

Review Employee Benefit Tables in WPS Spreadsheet

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel workbooks to review and edit benefit tables; WPS does not replace the Microsoft Power Query workflow described above.

Review and edit employee benefit tables in a familiar spreadsheet interfaceCompatible with Microsoft Excel formats, including XLSXMove existing spreadsheet files into a lightweight office suiteUse WPS Office for free
microsoft office alternative - wps office

Frequently Asked Questions

Will new benefit records create additional columns?

Yes. Add the records to the Benefits table and select Data > Refresh All. If an employee has more indexed benefit records than before, the query can create additional columns.

Why is a benefit field missing from the output?

Power Query's unpivot operation omits null values. If a particular indexed field is blank for every employee, its column may not be created. Check the source table and account for optional columns in downstream queries.

Can I keep benefit numbers consistent after a refresh?

Sort records within each employee group by a stable, unique benefit identifier before the AddIndexColumn operation. Without a stable sort, the same benefit may receive a different number after a refresh.

Will downstream queries continue to work when columns change?

Not necessarily. Queries and formulas that explicitly reference a missing or renamed benefit column can fail. Review those references after using Data > Refresh All and design downstream steps to handle optional columns.