Forum Discussion
Merge multiple queries depending on ID match
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 | |
| 6c3bd0e4-f248-41e8-b27c-bb46096bc061 | John | Wick | [email protected] |
| ce9fe849-c94d-4c6e-822c-b11bba50f116 | Petra | Parker | [email protected] |
| f61f4b5d-f4ec-4c12-bb0f-61a0bba26dda | Maxie | Moxie | [email protected] |
Users2
| uid | u_first | u_last | u_mail |
| 1e40df90-7017-4322-a70b-7a10a096c29c | John | Wick | [email protected] |
| f6e12d0e-43c6-4fd0-8dad-27eefdc48318 | Maxie | Moxie | [email protected] |
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 | uid | |
| 6c3bd0e4-f248-41e8-b27c-bb46096bc061 | John | Wick | [email protected] | 1e40df90-7017-4322-a70b-7a10a096c29c |
| ce9fe849-c94d-4c6e-822c-b11bba50f116 | Petra | Parker | [email protected] | |
| f61f4b5d-f4ec-4c12-bb0f-61a0bba26dda | Maxie | Moxie | [email protected] | f6e12d0e-43c6-4fd0-8dad-27eefdc48318 |
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.⚡
2 Replies
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.⚡
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.
