fbpx
Search
Close this search box.

How to extract common values in two tables? – Power Query Tip

Share

Facebook
Twitter
LinkedIn

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.

Extract common values from two tables - Excel

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:

Extract common values between two tables - Excel - howto?

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.

Merge - Power Query

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.

Merge two tables - Inner Join - Power Query

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.

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

2 Responses to “How to extract common values in two tables? – Power Query Tip”

  1. Varun Sharma says:

    Excel looks simple but it is very vast. You must know formulas and every single tab and their functioning. Thank you so much, Chandoo.

  2. Bhuvan says:

    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.

Leave a Reply