We love Joins and we hope you do too! If you’re not there yet, you should really check Part 1 of this series and learn why Power Query Join is as cool as a cold summer breeze. We looked at all the join types  in Power Query and what they can tell us. Ready to take things further? You’re in the right place: in this Part 2 we’ll use the different join types create a LOOKUP Dashboard, which will be a single table, but also a concept you can develop onwards.

Starting data

Our data will consist of two tables called HR and System. You can find the file we used here.

Table records can be matched up based on the common ID column. We also have a few records that can’t be matched up. We highlighted those records with green.

We bring both tables to Power Query with selecting a cell in the table and then From Table/Range.

We have all Join types from the previous Part 1 already in our Power Query, as in the image below.

Make new queries called Append1, Append2, …

We’ll create new queries following basically the same process. Let’s show it on the first example. We’ll create a query called Append1.

Select the query HR Join. Right click the query and select Reference.

Select ID column and then Transform > Count values.

We get the value 11, for 11 rows of ID column. Select To Table, to transform the query to a table.

Rename the name of the column from ID to Value.

Then select Add Column > Custom Column.

Set KPI as New column name and »Number of Rows in the HR table« in the Custom column formula.

Confirm with OK.

We get a new column.

Rename the query to Append1.

Append2 (System Join)

Now select System Join query and again create a Reference. Rename the query to Append2.

Repeat all the steps as in the Append1.

In the Custom Column dialog, set KPI as the new column name and »Number of Rows in the System table« as the Custom column formula.

Confirm with OK.

We have another finished query Append2.

Append3 (Inner Join)

Now reference Inner Join query and rename to Append3.

Repeat the process.

In the Custom Column dialog, set KPI as the new column name and »Number of Rows in the HR that did match« as the Custom column formula.

Confirm with OK.

We have another finished query Append3.

Append4

Now let’s do something a bit different, but very interesting.

Create a blank query and name it Append4.

Type in Append1 (it says Append2 on the image below but it’s really Append1) to the formula bar.

We get the second query table.

Select the 13 value and then right click and select Drill Down.

Rename this step to AllRows.

Insert new step with right click > Insert new step.

Type in Append3 to formula bar.

Again select the value 10 and then right click > Drill Down.

Rename the step to Matched Rows.

Insert new step. Type in the following formula into the formula bar.

= (AllRows – MatchedRows)/AllRows

Convert to table with To Table command.

Rename column name to Value.

Add a Custom column. In the Custom Column dialog, set KPI as the new column name and »Pct of non matches« as the Custom column formula.

Confirm with OK.

Append5 (HR Only)

Now let’s look at those non-matches.

Select HR Only query and again create a Reference.

Repeat all the steps from the Append1.

In the Custom Column dialog, set KPI as the new column name and »Number of IDs present only in HR« as the Custom column formula.

Confirm with OK.

Rename the query to Append5.

Append6 (System Only)

Similarly, select System Only query and again create a Reference.

Repeat all the steps from the Append1.

In the Custom Column dialog, set KPI as the new column name and »Number of IDs present only in System table« as the Custom column formula.

Confirm with OK.

Rename the query to Append6.

Create a Dashboard

At last, we arrive to the final step – creating a dashboard!

Select the last query, Append6 and then Append Queries as New.

Select all the Append queries and add them in the Append dialog, as on the image below.

We get the following result.

We switch the first and second column.

Rename the query to Dashboard.

Now select Close/Load.

Don’t yet load anything and then only select the Dashboard query to load (right click > Load to > Table). We get the following table.

What a beauty! The coolest thing is, it changes dynamically. So if the values in the original HR and System table change, the Dashboard value will automagicallly update too!

This is how we made a very informative overview of both tables in one Dashboard, using different join types in Power Query – just brilliant!

Watch the tutorial

Meanwhile, 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!