Compare 2 tables

Read about INTERSECT() and EXCEPT()

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 ,

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

Intersect in Power Query is basically an Inner Join.

Thank you for this clarification

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

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

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

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

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