We, humans like to compare. Whether we are on Facebook or workbook, we want to compare. So how do you compare two tables and extract common values? Simple, use Power Query. It can merge (a la join) tables and give you the common values.
If you just want to highlight matching values between two tables, See this tip.
Extract common values from two table – Step by Step tutorial
Let’s say you have two tables like this:
Load these tables in to Power Query. You end up with two queries – say Table1 and Table2.
Related: What is Power Query?
Now, go to Home ribbon of Power Query and click on Merge Queries > Merge Queries as New.
In the Merge options, set up the choices like this. You can hold CTRL to multi-select columns to join based on two or more columns.
Pro-tip: You can also apply other kinds of join. For example, use Left-Anti join to see values that are only in first table.
The result would be common values between two tables. You can load this data to Excel or to Data Model for further analysis.
Extract common values from two tables – Video Tutorial
What if you wanted to just highlight common values? What if you just want to compare based on a single column? Watch this video for all these scenarios and see solutions for simple to complex comparison problems.
You can see this and many other FREE Excel + Power BI tips at Chandoo.org YouTube Channel.
Common Values from two tables – Sample file
Please click here to download FREE sample workbook for this tip. Examine the Power Query steps to see how the two table are merged.
More ways to compare things in Excel…
I have thing for comparisons, when it comes to data. That is why over the years, I wrote many posts on this topic. Check out below to learn other ways to compare data in Excel.
- Compare two lists and highlight matching values – 5 second tip!!!
- Compare two lists using formulas – COUNTIFS to rescue
- Compare two cells and highlight matching words / phrases – VBA tip
- Match debits & credits – Pivot Table tip
How do you compare common values?
Power Query is my preferred method for comparing, merging or combining data. What about you? Are you hooked on Power Query yet? Please post your preferred method for comparing and extracting values in comments box.
2 Responses to “How to extract common values in two tables? – Power Query Tip”
Excel looks simple but it is very vast. You must know formulas and every single tab and their functioning. Thank you so much, Chandoo.
I thought earlier that I know most of about Excel. But after coming to this website, I realised that I know very little about Excel. So much to learn from you in Excel. Thank you.