Comparing Multiple values in two tables

[David Evans]

I have two Tables in Power Query, say Table 1 and Table 2.

They both contain many columns, but I am interested in identifying values in Date and Amount of Table 1 to see if they exist in Table 2.

Is PQ the best tool or am i barking up the wrong tree?

This challenge has been dogging me all day. I thought I'd paw-se and ask the real solution Hounds - appreciate any insight

Thanks

 

[Nebu]

Hi:

One way to do this is to merge the two tables using a unique field and pull the columns needed into a new output table.

Note:
Create the merge table as new merge table and populate the output in this table.

Thanks

 

[GraH - Guido]

Depending on the analysis at hand - if it is recurring or a one shot...

See if you would have in your start menu something called spreadsheet compare (I believe it exists since version 2013 or 2016).
Save both tables in a separate spreadsheet and load them in this tool.

Via Power Query, you could use List.Differences on each column [Date] and [Amount]. This would give a list of values that are in table1 and not in table2 (note: regardless of their position in the table).

 

[David Evans]

Depending on the analysis at hand - if it is recurring or a one shot...

See if you would have in your start menu something called spreadsheet compare (I believe it exists since version 2013 or 2016).
Save both tables in a separate spreadsheet and load them in this tool.

Via Power Query, you could use List.Differences on each column [Date] and [Amount]. This would give a list of values that are in table1 and not in table2 (note: regardless of their position in the table).

Interesting solution - will try it and report back to you.

 

[Chihiro]

Do a Left Outer Join as Nebu alluded to. You can use multiple column(s) as key column. Using Table1 as left, value not found in Table2 will return nulls.

Do note that duplicate value will create ambiguity. It is suggested, that you add index column before doing join operation to quickly identify duplicate instance.

 

[vrunda]

Via Power Query, you could use List.Differences on each column [Date] and [Amount]. This would give a list of values that are in table1 and not in table2 (note: regardless of their position in the table).

Please can you tell how to list the differences in power query. I merged the two tables in power query but how to find values in table1 & not in table 2 and vice versa