Power Query Problems

How to Align Manual Finance Data with a Dynamic Employee List in Power Query

Olivia MillerOlivia Miller Nov 10, 2026 870 views

Question details

The user needs a method to ensure manually entered finance data stays correctly associated with the right employees when the master list is dynamically updated, filtered, or sorted.

How to Align Manual Finance Data with a Dynamic Employee List
Product
Excel
Device & OS
not provided
Scenario
Managing a dynamically updating master employee list alongside manually entered finance data, such as commissions or bonuses.
Observed behavior
Manually entered finance data becomes associated with the wrong employee because it is stored by row position instead of being linked to a stable identifier when the master list is sorted or filtered.
Before you start

Ensure that both your master employee list and your manual finance data table contain a unique, stable identifier for each person, such as an Employee ID.

Solution 1Recommended

Use a Self-Referencing Query with a Stable Key

Use Power Query to merge your updated dynamic employee list with your existing finance data by utilizing a unique Employee ID.

Relying on row numbers for manual data entry often leads to misalignment when the underlying master data shifts due to sorting or filtering. To resolve this, you can use a self-referencing query.

This process allows Power Query to take a snapshot of your existing manual entries and merge them securely with the refreshed master list based on a unique key, ensuring that commissions or other finance metrics remain attached to the correct individual.

1
Assign a Stable Key

Ensure both your Employee Master data and your Finance data tables have a matching column containing a unique identifier, such as 'Employee ID'.

2
Load Tables into Power Query

Select your Employee Master table, go to the 'Data' tab on the ribbon, and click 'From Table/Range' to load it into Power Query. Repeat this step for your manual Finance data table.

3
Merge the Queries

In the Power Query Editor, select your Finance query. From the Home tab, click on 'Merge Queries'.

4
Define the Relationship

In the Merge dialog box, select the Employee Master query from the drop-down list. Click on the 'Employee ID' column in both the top and bottom previews to define the mapping, and set the Join Kind to 'Left Outer'.

5
Expand and Load

Click the expand icon at the top of the newly merged column to select the specific employee details you wish to view. Finally, click 'Close & Load' on the Home tab to export the aligned data back into your spreadsheet.

Use a Self-Referencing Query with a Stable Key
Data Alignment Secured: By merging data on a unique ID, your manual finance data will permanently remain linked to the correct employee, regardless of how the master list is updated or sorted.
Free Microsoft Office alternative

Manage Complex Spreadsheets with WPS Office

While advanced M-code Power Query features are specific to Microsoft Excel, WPS Office provides a lightweight, free, and highly compatible alternative for managing complex datasets. You can seamlessly use powerful lookup functions to align dynamic lists without heavy system resource consumption.

  1. 1. Download WPS Office: Install the free WPS Office suite from the official website.
  2. 2. Open Your Excel File: Launch WPS Spreadsheet and open your existing .xlsx workbook directly without format conversion.
  3. 3. Use Lookup Functions: Leverage built-in functions like XLOOKUP to dynamically link manual finance data to your employee list using Employee IDs.
Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) file formats.Built-in advanced formulas like VLOOKUP, INDEX/MATCH, and XLOOKUP for robust data merging.Lightweight architecture ensures fast loading speeds even with massive employee master lists.Free to download and use with a highly familiar, tabbed user interface.
QA img-9

Frequently Asked Questions

Why does my manual data shift to the wrong employee when sorting?

This occurs because the manual data is structurally tied to the spreadsheet's row position rather than the specific employee record. If the master list is sorted or filtered, the rows shift, but the manual data stays in place. Using a unique identifier links the data to the person, preventing this issue.

What is a stable key in Excel data management?

A stable key is a unique data point that never changes for a specific record. Examples include an Employee ID, Email Address, or Social Security Number. It is used to securely connect and match data across different tables.

Can I use VLOOKUP instead of Power Query to align this data?

Yes, for simpler datasets, you can use VLOOKUP or XLOOKUP. You can keep your manual finance data in a separate static table and use a lookup formula in your dynamic master list to pull in the finance data based on the Employee ID.