Forum Discussion
Keep Unmatched Data in Table with Relationship
I'm not sure if this is possible however I have two queries of 'leads' which have some of the same leads but not all.
Query 1: A list of emails that come into sytem 1, with name & Lead ID number. So this is a set of 100% of all emails. Each ID number is unique.
Query 2: Comes from system 2 where salespeople manually input leads from several sources (email, phone, online etc) inlcuding the name and lead ID. The problem: the sales team only input good emails into the query 2 source, not all 100%.
I can create a relationship by Lead ID between query 1 and 2.
My problem: Any visual that crosses query 1 and 2 only shows the matched leads that appear in query 2.
So: I have say 920 leads in query 1. If I create a table from query 1 only, I see a list of all 920 emails.
When I add fields from query 2, they match but now I only see the 880 emails ie only the subset that exists in query 2.
What I want is to identify those emails that DID NOT make it into query 2 data. So I'd like a table of all 920 emails, where the fields from query 2 (all leads) shows for matches, but is blank when there is no corresponding lead in query 2.
Is there a way to do this?
Please follow the link.
http://radacad.com/how-to-change-joining-types-in-power-bi-and-power-query
If this solves your issue please accept this as a solution and also give KUDOS.
Cheers
CheenuSing
Hi SharonHMA,
CheenuSing’s solution seems well, you can also use dax formula to get the result table.
Sample:
Table 1(Main table).
Table2.
Relationship:
Formula:
Table = NATURALLEFTOUTERJOIN(Table1,Table2)
If you want keep only the table2’s columns ,you can use selectcolumns to choose the display columns:
Table = SELECTCOLUMNS(NATURALLEFTOUTERJOIN(Table1,Table2),"Code",[Key],"Status",[Status])
Regards,
Xiaoxin Sheng
4 Replies
Please follow the link.
http://radacad.com/how-to-change-joining-types-in-power-bi-and-power-query
If this solves your issue please accept this as a solution and also give KUDOS.
Cheers
CheenuSing
Thanks CheenuSing I was thinking just in terms of relationships. did not even consider joining tables. this will help me compare data.
Hi SharonHMA,
CheenuSing’s solution seems well, you can also use dax formula to get the result table.
Sample:
Table 1(Main table).
Table2.
Relationship:
Formula:
Table = NATURALLEFTOUTERJOIN(Table1,Table2)
If you want keep only the table2’s columns ,you can use selectcolumns to choose the display columns:
Table = SELECTCOLUMNS(NATURALLEFTOUTERJOIN(Table1,Table2),"Code",[Key],"Status",[Status])
Regards,
Xiaoxin ShengThank you. your visuals make this very easy to understand.
