Compare 2 tables

[Mederic]

Hello,
I'd like to compare 2 tables, each in a different tab.
I would like to compare one by 2 criteria and another by all the columns.

How can I do this with Power query?

Thanks in advance

Table 1			
			
Code	date	Year	Qty
A	12/01/2024	2024	55
B	18/01/2024	2024	43
C	27/01/2024	2024	37
D	04/02/2024	2024	77

Table 2				
				
Code	date	Year	Qty	
A	12/01/2023	2023	55	
B	18/01/2024	2024	68	
C	27/01/2024	2024	37	
D	04/02/2023	2023	77	
E	11/02/2024	2024	23	
F	18/02/2024	2024	44	

[Mederic]

@lbendlin 

If you're talking about the @Anonymous 's code, it could be considerably improved, but it's difficult to do better than the video's code, which takes 2 or 3 seconds. And I don't need to go under 2 seconds. Thanks anyway for the suggestion

Best Regards

[lbendlin]

That code can be improved quite a bit. Recommend you run query diagnostics on it to see which bits are slow.

[Mederic]

Hello
@Anonymous , I've tested your code and it also works very well on this test file.

I just deleted the last step which removes the Duplicates and I get the expected result.
However, the code is very slow on my real file of more than 30,000 rows=>Table_1, more 6,000 rows=>Table_2 and I have to stop the process manually.

@lbendlin , the Version 1 in the video is very fast and works very well

Here is the code used:

let
    Source = Table.NestedJoin(Table_1, {"Code", "Qty"}, Table_2, {"Code", "Qty"}, "Table_2", JoinKind.Inner),
    InnerJoin1 = Table.SelectColumns(Source,{"Code", "date", "Year", "Qty"}),
    #"Added Custom" = Table.AddColumn(InnerJoin1, "Custom", each "Table_1"),
    Custom1 = Table.NestedJoin(Table_2, {"Code", "Qty"}, Table_1, {"Code", "Qty"}, "Table_1", JoinKind.Inner),
    InnerJoin2 = Table.SelectColumns(Custom1,{"Code", "date", "Year", "Qty"}),
    #"Added Custom1" = Table.AddColumn(InnerJoin2, "Custom", each "Table_2"),
    #"Full Inner Join" = Table.Combine({#"Added Custom", #"Added Custom1"}),
    #"Sorted Rows" = Table.Sort(#"Full Inner Join",{{"Code", Order.Ascending}}),
    #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"Custom", "Source"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Source", type text}})
in
    #"Changed Type"

Thank you 2 for your solutions

Have a nice evening
Best Regards

[Mederic]

Hello @Anonymous ,

Thank you for your message and the code,
I'll test it this evening when I get home,
I will also test the speed of execution over 30,000 rows with all versions of this thread.

Best regards

[Mederic]

Hello @lbendlin ,

Thank you very much for the video with 2 interesting versions,

I've chosen "Inner" instead of "Left Anti" and I'm getting the result I wanted.
Here is the code i used :

let
    Source = Table.NestedJoin(Table_1, {"Code", "Qty"}, Table_2, {"Code", "Qty"}, "Table_2", JoinKind.Inner),
    AntiJoin1 = Table.SelectColumns(Source,{"Code", "date", "Year", "Qty"}),
    #"Added Custom" = Table.AddColumn(AntiJoin1, "Custom", each "Table_1"),
    Custom1 = Table.NestedJoin(Table_2, {"Code", "Qty"}, Table_1, {"Code", "Qty"}, "Table_1", JoinKind.Inner),
    AntiJoin2 = Table.SelectColumns(Custom1,{"Code", "date", "Year", "Qty"}),
    #"Added Custom1" = Table.AddColumn(AntiJoin2, "Custom", each "Table_2"),
    #"Full Anti Join" = Table.Combine({#"Added Custom", #"Added Custom1"}),
    #"Sorted Rows" = Table.Sort(#"Full Anti Join",{{"Code", Order.Ascending}}),
    #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"Custom", "Source"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Source", type text}})
in
    #"Changed Type"

Have a nice day

Best Regards

[Mederic]

Sorry, I meant Except and not Intersect in my message 5, I've corrected it.

[Anonymous]

Hi @Mederic 

You can refer to the following solution.

I used the paramater

Then you can put the following code to advanced editor 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc27DcAwCATQXagtOWAc3Oa7BPL+a8RAinyqO/GkQxUWSICUMdNEZfQ7aoWeFFbTFsqhFnNz3ewiPy3iuo/KmR5owxJ42DC+1KO4nvH2q8zQ+wU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, date = _t, Year = _t, Qty = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"date", type text}, {"Year", Int64.Type}, {"Qty", Int64.Type}})
in
    #"Changed Type"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI00jcw1DcyMDIBcqCUqalSrE60khNI2kIfXdbEGCzrDBIxx5A1NgfLuoAU6huhSZoDJWMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, date = _t, Year = _t, Qty = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"date", type text}, {"Year", Int64.Type}, {"Qty", Int64.Type}}),
    #"Appended Query" = Table.Combine({#"Changed Type", Query1}),
    #"Added Custom" = Table.AddColumn(#"Appended Query", "Custom", each let a=Table.RowCount(Table.SelectRows(#"Appended Query",(x)=>x[Code]=[Code] and x[Qty]=[Qty])),
b=Table.RowCount(Table.SelectRows(#"Appended Query",(x)=>x[Code]=[Code] and x[Qty]=[Qty] and x[date]=[date] and x[Year]=[Year]))
in if Parameter1=1 and a>1 then 1 else if Parameter1=2 and b>1 then 1 else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
    #"Removed Duplicates"

When the paramater is equal to 1, it will filter the data by code and qty in query1 table, when the paramater equal to 2, it will filter the data by all columns in query 1 table.

equal 1.

equal 2

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin · Accepted solution]

you mean like this? 🙂

Performing a full Anti Join in Power Query - Compare Two Tables - YouTube

[Mederic]

Thank you for this clarification

[lbendlin]

Intersect in Power Query is basically an Inner Join.

[Mederic]

@lbendlin ,

Yes, I didn't understand how to use Except in Power Query
Otherwise, I'm looking for a more elegant solution for version 1.

Best Reagards

[Mederic]

Hello @lbendlin ,

Thank you for your message,
For version 1, I went through 2 queries and I get an acceptable but not very elegant result,
For version 2, I used Intersect and got a decent result.

Finally, for the version 2, it doesn't make sense to have duplicates. One row is enough for me.

Any other solution is welcome

Thanks in advance

Best  Regards

Result V1 :

Query 1 :

let
    Source = Table.Combine({Table_1, Table_2})
in
    Source

Query 2 :

let
    Source = Table.NestedJoin(Query2, {"Code", "Qty"}, Query1, {"Code", "Qty"}, "Query1", JoinKind.LeftAnti),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Query1"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Code", Order.Ascending}})
in
    #"Sorted Rows"

Result V2 :

let
    Source = Table.FromRecords(List.Intersect({Table.ToRecords(Table_1), Table.ToRecords(Table_2)})),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}})
in
    #"Changed Type"

[lbendlin]

Apologies, I gave the wrong advice based on DAX.  But it looks like you found the Power Query solutions yourself.  What exactly do you still need help with?

[lbendlin]

Read about INTERSECT() and EXCEPT()