Identify entries not present in 2nd table

[Account6910]

Hi, I have two tables:

• Table 1 is a list of transactions with many columns EG "AuditTrail" , "£", "Nominal Account" etc

• Table 2 is derived from a number of project files and essentially allocates audit trail transactions against different projects, for simplicity it has two columns "UniqueProjectCostRef" & "AuditTrail"

The aim is to analyse the "AuditTrail" transactions that are not matched against a "UniqueProjectCostRef".

In PowerQuery I can merge Table 2 into Table 1, expand the "UniqueProjectCostRef" then in my PowerBi Matrix filter by null. But I want to avoid unnecessary merges

So Far I have:

1. Created a relationship between the two tables using the "AuditTrail" columns.

2. Created a matrix visualisation with 2 rows(Table1[AuditTrail]), (Table2[UniqueProjectCostRef]), and 1 value (Table1[£])

3. On the (Table2[UniqueProjectCostRef]) row I have selected "Show items with no data"

This creates a table with 50,000 audit trail transactions matched relevant unique cost code & that shows blanks where not matched - how do I filter this to only show the gaps?

[amitchandak · Accepted solution]

@Account6910 , You can use anti join in power query

https://docs.microsoft.com/en-us/power-query/merge-queries-left-anti

Except - https://powerbidocs.com/2020/11/10/except-dax-function-in-power-bi/

Check isblank and common dimension.

Just have the same period data and use the lost formula 

https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...