Urgent Help on look up function for non matching records.

[Bansi008]

Hi,

I have two tables with identifiers. I want to populate non-matching records from table 2 compared to table 1. I am ok to generate non matching records in the same table or an existing table, whichever suits the dynamic solution. 

Sample data is provided below and non matching records are highlighted in RED. Can someone please help how could I populate non matching records compared to table using identifier column.

Thanks!

[Bansi008]

Hi,

Thanks for your response. Can you please explain step wise guide. 
I tried append queries option but then did result was not as per expection. Could you please help which option to be use from transform view to use given DAX query. 

[MFelix]

Hi @Bansi008 ,

Youhave both table on the Power Query correct?

On table 1 do the following steps:

• Append Table 2

 

• Select all the columns on the table
• Remove duplicates

If you want to do it in DAX then you need to do something similar to this:

	 UNION(
	 	'Table 1',
		EXCEPT(
			'Table 2',
			'Table 1'
		)
	 )

Be carefull the order of the tables in the except is important.

[MFelix · Accepted solution]

Hi @Bansi008 ,

If all the 3 columns on both tables are the same believe best option is to do an Append of both tables and then remove duplicates based on all columns selected:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Hci7DQAwCAPRXahThI+nQSj7bxGb5nR63eb3Pne3o1P5c9qCHhGUWNfLk56ZlFzXy4teVZRa18tBB0DBun7mAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [IDENTIFIER = _t, ACCOUNT_ID = _t, SEC_ID = _t]),
    #"Appended Query" = Table.Combine({Source, #"Table 2"}),
    #"Removed Duplicates" = Table.Distinct(#"Appended Query")
in
    #"Removed Duplicates"