Excel Unplugged

Why You Should Know Power Query – Part 3: Merge

Power Query is a great tool for data transformation. I often use as an upgrade to Excel. We already covered two great functionalities in first two parts: fill and split. Let’s look at another one: Power Query merge or join or lookup, or whatever you want to call it. We have two tables and we would like to match it up with another table based on a common column.

How-to

Let’s look at the following two tables. We’ll call them Main table and Lookup table. Notice there are multiple repeating IDs in the Lookup table (on the right) and only unique ID values in our Main table (left).

Bubble chart

Description automatically generated with low confidence

We would like to get the corresponding Points Weight value for each ID in our Lookup (left) table. Using lookup function would only return the first found value from the lookup table (for example, 0,94 for John). We would like to get all four corresponding rows for John, not just the first value.

Let’s load the tables to Power Query.

Select the Main table and then select Merge Queries.

Graphical user interface, application, table, Excel

Description automatically generated

Set the Lookup table and select ID column in both tables.

Set Join Kind to Left Outer.

Confirm with OK.

Graphical user interface, application, table

Description automatically generated

We get the following result. Each row has a new table.

Table

Description automatically generated

We expand the column with the Points Weight column.

Graphical user interface, application, table, Excel

Description automatically generated

We now have all 4 rows for John.

Table

Description automatically generated

Brilliance! I use this sort of merge in Power Query all the time and hopefully now you will too.

This sums up our Why You Should Know Power Query Series. Hopefully you’ve added these useful tricks to your Excel tool belt!

Watch the tutorial

You can also watch the tutorial online on our YouTube channel!

Please leave us a like, comment, and subscribe for more amazing Excel tricks!

Follow us on LinkedIn.

Check out R Academy!