Excel Unplugged

Join Types in Power Query – Part 2: Lookup Dashboard

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.

Table

Description automatically generated

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.

Graphical user interface, table

Description automatically generated

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.

Table

Description automatically generated

Select ID column and then Transform > Count values.

Graphical user interface, table, Excel

Description automatically generated

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

Graphical user interface, application

Description automatically generated

Rename the name of the column from ID to Value.

Graphical user interface, application

Description automatically generated

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.

Graphical user interface, application

Description automatically generated

We get a new column.

Graphical user interface, text, application

Description automatically generated

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.

Graphical user interface, text, application

Description automatically generated

We have another finished query Append2.

Graphical user interface, text, application

Description automatically generated

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.

Graphical user interface, application

Description automatically generated

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.

Graphical user interface

Description automatically generated

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

Rename this step to AllRows.

Graphical user interface, application

Description automatically generated

Insert new step with right click > Insert new step.

Type in Append3 to formula bar.

Graphical user interface, application

Description automatically generated

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

Graphical user interface, text, application

Description automatically generated

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.

Graphical user interface, text, application

Description automatically generated

Confirm with OK.

Graphical user interface, application

Description automatically generated

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.

Graphical user interface, application

Description automatically generated

Confirm with OK.

Rename the query to Append5.

Graphical user interface, text, application, email

Description automatically generated

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.

Graphical user interface, application

Description automatically generated

Confirm with OK.

Rename the query to Append6.

Graphical user interface, text, application

Description automatically generated

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.

Graphical user interface, application, table

Description automatically generated

We get the following result.

Graphical user interface, table

Description automatically generated

We switch the first and second column.

Graphical user interface

Description automatically generated

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.

Graphical user interface, application, table, Excel

Description automatically generated

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!