Merge multiple queries depending on ID match

[DennisGaida]

I'm thinking I have to do some kind of UNION, but I can't figure it out. The blog post Combine multiple tables with UNION / UNION ALL in ... - Microsoft Power BI Community helped, but I didn't get very far.

I have three tables:

Connections

connectionid1_value	entityid
6c3bd0e4-f248-41e8-b27c-bb46096bc061	f942f7c1-4c5a-44cd-a11a-3900904ce733
1e40df90-7017-4322-a70b-7a10a096c29c	f942f7c1-4c5a-44cd-a11a-3900904ce733

Users1

userid	firstname	lastname	email
6c3bd0e4-f248-41e8-b27c-bb46096bc061	John	Wick	johnwick@example.org
ce9fe849-c94d-4c6e-822c-b11bba50f116	Petra	Parker	petraparker@example.org
f61f4b5d-f4ec-4c12-bb0f-61a0bba26dda	Maxie	Moxie	maxiemoxie@example.org

Users2

uid	u_first	u_last	u_mail
1e40df90-7017-4322-a70b-7a10a096c29c	John	Wick	johnwick@example.org
f6e12d0e-43c6-4fd0-8dad-27eefdc48318	Maxie	Moxie	maxiemoxie@example.org

Basically I want to join the connections to the users - each ID in connectionid1_value corresponds to another ID in either the Users1 or Users2 table. And the either part is what is throwing me off.

I can merge the Users1 and Users2 table in the Power Query editor (leveraging the e-mail as primary key) into MergedUsers, but I can't seem to figure out the missing link, because when I would try to do two joins (connections.connectionid1_value > MergedUsers.userid & connections > MergedUsers.uid) I can only do a many:many relationship which doesn't seem right.

How do I map the GUID in connectionid1_value to either column userid or column uid depending on where it is matched (this is when using the MergedUsers table)?

For completeness sake the MergedUsers table (note that not all users have a uid / are in the Users2 table):

userid	firstname	lastname	email	uid
6c3bd0e4-f248-41e8-b27c-bb46096bc061	John	Wick	johnwick@example.org	1e40df90-7017-4322-a70b-7a10a096c29c
ce9fe849-c94d-4c6e-822c-b11bba50f116	Petra	Parker	petraparker@example.org
f61f4b5d-f4ec-4c12-bb0f-61a0bba26dda	Maxie	Moxie	maxiemoxie@example.org	f6e12d0e-43c6-4fd0-8dad-27eefdc48318

[DennisGaida]

This got me on the right track, thank you!

I created an append query, but also a merge query. The append query I can use to filter based on a single userid, the merged query I can use to get all the information on the particular user from all user tables.

[parry2k · Accepted solution]

@DennisGaida you can append both tables together in PQ, before you append, make sure column names are the same and this will create Union of both the tables and then you will only set relationship with UserId.

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡