Merging Table - Using Merged Table & Original Unmerged Dataset

[Dillard70]

I work in the mortgage industry and am trying to analyze data on files originating from purchased leads, and files that originated from all other sources (generally referrals from previous clients or realtor partners). 

I have two datasets in excel/CSV format. One is all the information from our lead aggregator and the other is a report generated from Encompass, the system we use for all of our mortgage applications. Neither one of these sources is very accurate at showing which applications came from purchased leads, because both are dependent on all users to accurately input data, which of course never happens. So, what I had to do was take my Encompass dataset and merge the info needed with the lead aggregater dataset by customer phone number (this was the only distinct field available in both systems). I now have a merged report that shows all data from both sets for clients that were represented in both sets, but not clients who were on one but not the other. 

Now, I need to compare data in the merged dataset with data from the unedited datasets, but I do not want the unedited sets to display data that exists on the merged dataset. So, if I want to show the total number of closed loans that originated from purchased leads, I can just use the merged set and create a visualization with the number of closed loans by month. However, I do not know how to show loans that closed that did NOT originate from purchased leads, because the original, unedited dataset from Encompass includes every closed loan regardless of the origin of the lead. 

Is there a way to filter out a report by shared data? Or, is there a way to create another table with the leftover data from the two merged reports? Here is a crude example:

Dataset X contains:

Columns A - H

Rows X1-X10

Datset Y contains:

Columns G - L (where columns G and H share similar data with Dataset X's columns G and H)

Rows Y1-Y10

Merged Dataset (by Column G):

Columns A - L

Rows Z1-Z5 (these are the rows that shared data in column G)

So now I need to filter Datasets X and Y to only show the rows that we not added to the Merged Dataset. 

[danextian · Accepted solution]

Hi @Dillard70,

In Power Query, there isn't just one kind of merge. There are actually 6. The most common of them is Left Outer which is very similar with Excel's VLOOKUP. 

If your merge query returned just the matching row between the two tables, the what you did was most probably an Inner join.

If you need to find the rows tht are in the first but not in the second table, you may use Left Anti. Otherwise, use Right Anti.