Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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"
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi,
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.
Hi @Bansi008 ,
Youhave both table on the Power Query correct?
On table 1 do the following steps:
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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"
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
94 | |
86 | |
78 | |
70 | |
63 |
User | Count |
---|---|
114 | |
101 | |
97 | |
66 | |
59 |