Replace Usernames with Employee Names in Excel Using Power Query
Question details
Automatically replace usernames written by Power Automate with matching employee names from a separate reference table.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- A Power Automate flow adds username values to an Excel workbook, and the workbook needs to display the corresponding employee names from a username-to-name lookup table.
- Observed behavior
- Replacing values with formulas in the same output area can cause dynamic-array spill errors. The intended result is a separate transformed table containing employee names.
Format both the Power Automate output and the username-to-employee-name reference range as Excel Tables with clear, unique column headers. Confirm that the username values use the same spelling and format in both tables before creating the query.
Merge the Power Automate table with the employee reference table in Power Query
Use this method to create a refreshable employee-name output table without placing spilling formulas inside the Power Automate data range.
Power Query keeps the incoming Power Automate table unchanged, looks up each username in a separate reference table, and loads the transformed results to another worksheet. Refresh the query whenever Power Automate adds new rows.
Select the Power Automate output range, then choose Insert > Table and give it a name such as AutomationOutput on the Table Design tab. Create a second table for the username and employee-name mapping, such as EmployeeReference.
Click a cell in AutomationOutput and select Data > From Table/Range. In Power Query, choose Home > Close & Load To > Only Create Connection. Repeat Data > From Table/Range for EmployeeReference and also load it as a connection.
In Excel, open Data > Queries & Connections, double-click the AutomationOutput query, then select Home > Merge Queries. Choose EmployeeReference as the second table, select the Username column in both tables, and use the Left Outer join type.
Select the expand button on the merged EmployeeReference column, select the Employee Name field, clear Use original column name as prefix if desired, and click OK. Remove the original Username column if the output should show only employee names.
Select Home > Close & Load To, choose Table, and select New worksheet or an empty destination outside the Power Automate output table. Use Data > Refresh All after the flow writes new usernames.
Use WPS Office for lightweight Excel-compatible spreadsheet work
If you need a free, lightweight office suite for everyday spreadsheet editing, WPS Office provides a familiar spreadsheet interface and supports seamless migration of common Microsoft Excel files. You can open existing workbooks, review tables, and continue standard data-cleaning work with familiar tools.
Frequently Asked Questions
Why does a formula return a spill error when I replace usernames in an Excel table?
Dynamic-array formulas need empty cells for their results, but an Excel Table or the Power Automate output range may already occupy those cells. Load the transformed data to a separate worksheet with Power Query instead.
Which join type should I choose when merging usernames with employee names?
Choose Left Outer in Home > Merge Queries when the Power Automate table is the first table. This keeps every incoming row and adds an employee name where a matching username exists.
What happens when a username has no match in the employee reference table?
The expanded Employee Name value will be null or blank for that row. In Power Query, you can use Transform > Replace Values or Add Column > Conditional Column to display a label such as "Employee not found."
How do I update employee names after Power Automate adds new rows?
Make sure Power Automate writes into the existing named Excel Table, then select Data > Refresh All in Excel. Power Query will read the latest rows and apply the merge again.
Can the source username column remain in the Power Query result?
Yes. After expanding the merged employee-name field, keep both Username and Employee Name columns for auditing, or select the Username column and choose Home > Remove Columns to show names only.
