DAX assistance needed

[TK1966]

Hello there!

I am basically looking for the following solution

I have a table with the following columns

Employee number

supervisor number

Regional manager

Only the regional manager fields are completed for the supervisors. However i am trying to take the regional manager value of the supervisor and then complete the blanks for all of the employee names. i have thousands of employees and 100s of managers so need some form of lookup? I am not sure

[TK1966]

Superb help! Thank you so much!

[pcoley · Accepted solution]

@TK1966 Please try doing it with powerquery:

You have a table where:

• Every row has an Employee number
• Many rows have a supervisor number (the person they report to)
• Only supervisors have the Regional manager filled in
• Regular employees have Regional manager blank
• Goal: propagate each employee's supervisor's Regional manager value into their own row

Recommended Solution: Self-Merge (Join the table to itself)

This is clean, performant, and handles the logic correctly even if the hierarchy has multiple levels (as long as regional managers are only filled at the supervisor level).

Steps in Power Query Editor:

1. Start with your table loaded (let's call the query Employees).

2. Duplicate the query (right-click → Duplicate) and name the duplicate something like Supervisors or RM_Lookup. → This will be the "lookup table" containing only the rows where Regional manager is known.

3. In the duplicated query (RM_Lookup), do these clean-up steps:

   • Keep only these columns: Employee number, Regional manager
   • Rename "Employee number" → supervisor number (very important!)
   • Remove duplicates (if any) → Home tab → Remove Rows → Remove Duplicates
   • Optionally filter out nulls in Regional manager if you want to be extra safe

   Now this table contains: supervisor number | Regional manager (only rows where the regional manager is actually filled)

4. Go back to your main Employees query.

5. Merge Queries (Home tab → Merge Queries → Merge Queries as New or directly into current):

   • Top table: your main Employees table
   • Bottom table: the RM_Lookup table you just prepared
   • Join on: supervisor number (from main table) = supervisor number (from lookup table)
   • Join Kind: Left Outer (very important – keeps all employees)
   • Click OK

6. Expand the new merged column (click the expand icon):

   • Select only Regional manager
   • Uncheck "Use original column name as prefix"
   • OK

7. You now have two Regional manager columns:

   • The original one (which is mostly blank)
   • The new one coming from the supervisor (filled where it exists)

8. Combine them with a custom column (Add Column → Custom Column):

   if [Regional manager] <> null and [Regional manager] <> "" 
   then [Regional manager] 
   else [Regional manager.1]

   Or more concisely (handles nulls better):

   if [Regional manager] is null then [Regional manager.1] else [Regional manager]

   Name it e.g. Final Regional Manager

9. Remove the two intermediate Regional manager columns if you want.

10. (Optional) Rename Final Regional Manager → Regional manager

-->It looks up each employee's supervisor number in the list of supervisors → brings back their Regional manager value. This is exactly the "take the regional manager value of the supervisor" logic you described.

------------
Alternative (if you prefer no duplicate query)

You can do it with a single merge by using the same table twice:

Home → Merge Queries → Select your table as both top and bottom → Join supervisor number (left) to Employee number (right) → Left Outer join → Expand only Regional manager from the right side

Then proceed from step 7 above.

I hope this helps. If so please mark it as a solution. Kudos are welcome.