Power Query Merge with conditions/filter

[Crazy_FC]

My problem comes with a merge between Employees table and Home Location table. I have an employee list as my main table that I am joining to and I need the Home location based on where the employee worked the most amount of hours. The Home Location table takes all of the hours for the time frame, groups it by employee and then ranks it with 1 being the location they have the most and filters to 1.

I merged the Home Location table with the Employee table using the following merge: "Merged Queries5" = Table.NestedJoin(#"Added Custom5", {"Employee ID"}, Table.SelectRows(#"Home Office", each [Loc Rank by Emp] = 1), {"Employee ID"}, "Home Office", JoinKind.LeftOuter). I have a number of employees that have a home location of something other than their ranked 1 location so either the filter in the Home Location table or the way I've merged it is not working and I cannot figure out what the issue is.

Due to the sensitive nature of the data I cannot provide a spreadsheet to help illustrate.

Any direction will help.

 

[I_cant_read]

Try the ff.
1.) Query 1 is the List of Employee ID and Employee details.
2.) Query 2 is the list of homes which are ranked 1 as the most number of hours the employee is there etc.
Why not add a step in Query 2 and just filter the column containing the rank 1. Then load it as a connection.

3) Merge Query 1 to Query 2 using Employee ID then expand the table.

Cheers