Power BI | Multiple tables exercise | Changing the join type between tables

This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.

The answer to the exercise will be included and explained if you attend the course listed below!

Software ==> Power BI  (57 exercises)
Version ==> Latest update
Topic ==> Multiple tables  (1 exercise)
Level ==> Relatively easy
Course ==> Introduction to Power BI
Before you can do this exercise, you'll need to download and unzip this file (if you have any problems doing this, click here for help).

You need a minimum screen resolution of about 700 pixels width to see our exercises. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Make a table holding a list of dinosaurs and their preferred diet, which surprisingly isn't Atkins. Dino Diets.xlsx in the above folder will help!

Joins Relationships inner join

Some of these dinosaurs appear to eat the same as size 0 models (nothing)!

The reason some of these are empty is due to the Dino.DietID not having a corresponding Food.DietID. What about the missing Diets? Go to the field well:

Relationships full outer join.

Ticking Show items with no data will unhide any missing food types.

Now in the table you should see all the values from both tables shown: dinos with an unknown diet, and diets with no dino to eat them:

Relationships full outer join

See food like Sea food? Eats all the food it sees, get it?

That was how to show all the results. What if we don't want blanks? Getting rid of Diet blanks is easy. Simply untick Show items with no data:

Relationships inner join

Sadly it isn't as straight forward for Dino as we can't untick anything.

Open the Query editor and choose Merge Queries:

Relationships Merge queries

Merge queries will filter the table you have highlighted (Dino) whilst Merge Queries as New will make a new filtered table.

We want to remove all the dinosaurs who haven't got a listed food source  In this case we've chosen Merge queries to filter the existing table. Click the two DietID columns:

Joins left outer inner

The default kind of join was Left Outer Join, which was returning all dinosaurs but only matching diets. Change the join type to Inner.

 

Using an inner join will mean that only dinosaurs with known diets will be shown. Similarly only diets eaten by a known dinosaur will be shown (we can assume the rest are extinct).

Relationships inner join

This does mean you can't change your mind however. Trying to use the show data option now will have no affect on which dinosaurs can be seen.

 

Optionally save this as A diet to die for.pbix and close it down. 

This page has 0 threads Add post