Keep Unmatched Data in Table with Relationship

[SharonHMA]

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?

[CheenuSing · Accepted solution]

@SharonHMA

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

[v-shex-msft · Accepted solution]

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

[SharonHMA]

Thank you. your visuals make this very easy to understand.

[CheenuSing · Accepted solution]

@SharonHMA

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

[SharonHMA]

Thanks CheenuSing I was thinking just in terms of relationships. did not even consider joining tables. this will help me compare data.