Spreadsheet: 676870d1589028070-power-query-merging-usersandunits
Sheet: Sheet1

Units |  |  |  |  | Users |  |  | UnitOwners |  |  |  | Affiliations
UnitID | UnitName |  |  |  | UserID | UserNumber |  | Unit | [NAME] | Relation |  | Users | Unit | IsPrimaryUnit
1 | Unit1 |  |  |  | XX22XX | 476 |  | 1 | 476 | 17 |  | XX22XX | 1 | 1
2 | Unit2 |  |  |  | VV44VV | 934 |  | 1 | 324 | 8 |  | XX22XX | 2 | 0
3 | Unit3 |  |  |  | KK33KK | 578 |  | 2 | 934 | 10 |  | VV44VV | 1 | 0
3 | Unit3 |  |  |  | PP99PP | 324 |  | 2 | 476 | 5 |  | VV44VV | 2 | 1
4 | Unit4 |  |  |  |  |  |  | 3 | 578 | 17 |  | VV44VV | 3 | 0
 |  |  |  |  |  |  |  | 4 |  |  |  | KK33KK | 3 | 1
 |  |  |  |  |  |  |  |  |  |  |  | KK33KK | 4 | 0
 |  |  |  |  |  |  |  |  |  |  |  | PP99PP | 1 | 1
Units should have 2 new colums (the red ones): |  |  |  |  |  |  |  |  |  |  |  | Affiliations should have 2 new columns (the red ones):
OwnerID: lookup the OwnerNumber on the Unit in UnitOwners, prioritized with Relation 8 if avaliable, otherwise Relation 17 if avaliable. All other relations are not relevant. Then lookup the UserID on that UserNumber in Users |  |  |  |  |  |  |  |  |  |  |  | PrimaryUnit: Shows the UnitName of the Unit that is the primary for the user, ie 1 in IsPrimaryUnit. There will only be one primary unit for each user and it should be listed on all rows of that user.
TotalUsers: Total number of Users on that Unit in Affiliations |  |  |  |  |  |  |  |  |  |  |  | AllUnits: List the UnitNames of all the Units the user has in Affiliations
UnitID | UnitName | OwnerID | TotalUsers |  | Explaination on OwnerID: |  |  |  |  |  |  | Users | Unit | IsPrimaryUnit | PrimaryUnit | AllUnits
1 | Unit1 | PP99PP | 3 |  | Relation 8 on Unit 1 in UnitOwners is OwnerNumber 324, which is PP99PP in Users |  |  |  |  |  |  | XX22XX | 1 | 1 | Unit1 | Unit1, Unit2
2 | Unit2 | VV44VV | 2 |  | No Relation 8 on Unit 2, but Relation 17 is 934, ie VV44VV |  |  |  |  |  |  | XX22XX | 2 | 0 | Unit1 | Unit1, Unit2
3 | Unit3 | KK33KK | 2 |  | No Relation 8 on Unit 3, but Relation 17 is 578, ie PP99PP |  |  |  |  |  |  | VV44VV | 1 | 0 | Unit2 | Unit1, Unit2, Unit3
4 | Unit4 |  | 1 |  | No owner with Relation 8 or Relation 17, so this should be empty |  |  |  |  |  |  | VV44VV | 2 | 1 | Unit2 | Unit1, Unit2, Unit3
 |  |  |  |  |  |  |  |  |  |  |  | VV44VV | 3 | 0 | Unit2 | Unit1, Unit2, Unit3
 |  |  |  |  | Explaination on TotalUsers |  |  |  |  |  |  | KK33KK | 3 | 1 | Unit3 | Unit3, Unit4
UnitID | UnitName | OwnerID | TotalUsers |  | Unit 1 has 3 total users in Affiliations, ie XX22XX, VV44VV and PP99PP |  |  |  |  |  |  | KK33KK | 4 | 0 | Unit3 | Unit3, Unit4
1 | Unit1 | PP99PP | 3 |  | Unit 2 has 2 total users in Affiliations, ie XX22XX and VV44VV |  |  |  |  |  |  | PP99PP | 1 | 1 | Unit1 | Unit1
2 | Unit2 |  | 2 |  | Unit 3 has 2 total users in Affiliations, ie VV44VV and KK33KK
3 | Unit3 | KK33KK | 2 |  | Unit 4 has 1 total users in Affiliations, ie KK33KK
4 | Unit4 |  | 1 |  |  |  |  |  |  |  |  | UserID | Unit | IsPrimaryUnit | PrimrayUnit | AllUnits
 |  |  |  |  |  |  |  |  |  |  |  | XX22XX | 1 | 1 | Unit1 | Unit1, Unit2
 |  |  |  |  |  |  |  |  |  |  |  | XX22XX | 2 | 0 | Unit1 | Unit1, Unit2
 |  |  |  |  |  |  |  |  |  |  |  | VV44VV | 1 | 0 | Unit2 | Unit1, Unit2, Unit3
 |  |  |  |  |  |  |  |  |  |  |  | VV44VV | 2 | 1 | Unit2 | Unit1, Unit2, Unit3
 |  |  |  |  |  |  |  |  |  |  |  | VV44VV | 3 | 0 | Unit2 | Unit1, Unit2, Unit3
 |  |  |  |  |  |  |  |  |  |  |  | KK33KK | 3 | 1 | Unit3 | Unit3, Unit4
 |  |  |  |  |  |  |  |  |  |  |  | KK33KK | 4 | 0 | Unit3 | Unit3, Unit4
 |  |  |  |  |  |  |  |  |  |  |  | PP99PP | 1 | 1 | Unit1 | Unit1