Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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
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
Thank you. your visuals make this very easy to understand.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
99 | |
82 | |
67 | |
63 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |