Just wondering this...if I had two tables of data - one with a list of ALL possible customers and another of customers with invoices - how could I show a list of only the customers with whom I have no invoices. I know you can do it in Access with the outer join (or maybe is it inner join) option in table relationships but can it be done in Power Pivot? Thanks.
Hi Anne,
Session 3.04 shows you what the different join types do, but from memory it's Left Anti.
Mynda
Let me check that out 🙂 Thanks a mil.
Had a quick check. It doesn't seem to be in that tutorial. If I had two Excel files, how would I show the customers with no invoices..would it be via a measure or how would I implement the Left Anti you have mentioned above? Thanks 🙂
Sorry, Anne. I was referring to Power Query. Should check my 'powers' before replying.
I explain how to use Power Query to compare lists here:
https://www.myonlinetraininghub.com/excel-compare-two-lists
Alternatively, in Power Pivot you could use the RELATED function to add a column to Table 1 that brings in the Invoice Number from Table 2, then filter your PivotTable to only show blank 'Invoice Numbers' in Table 1.
Mynda
