EXERCISE TOPIC▼
POWER BI EXERCISES▼
POWER BI EXERCISES▼
- Basic reports (10)
- Data sources (2)
- Query editor (8)
- Multiple tables (1)
- Filtering data (9)
- Drill-through filtering (1)
- Bookmarks (4)
- Charts (10)
- Other types of visualisation (1)
- Overview of maps (6)
- Calculated columns (7)
- Introduction to DAX (3)
- Creating measures in DAX (1)
- Calendars (2)
- Date functions (2)
- Roles and security (1)
- Advanced data models (4)
- Drill-through and bookmarks (3)
- Custom visuals (3)
- Parameters (5)
- Quick measures (3)
- Report themes (2)
- Power BI mobile (1)
- Power BI Templates (1)
- Tooltips (2)
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.
You can learn how to do this exercise if you attend one of more of the courses listed below!
| Software ==> | Power BI (92 exercises) |
| Version ==> | Latest update |
| Topic ==> | Multiple tables (1 exercise) |
| Level ==> | Relatively easy |
| Classroom ==> | Introduction to Power BI / Fast track Power BI and DAX / Fast track Power BI |
| Online ==> | Introduction to Power BI / Fast track Power BI and DAX / Fast track Power BI |
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!
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:
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:
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:
Sadly it isn't as straight forward for Dino as we can't untick anything.
Open the Query editor and choose 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:
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).
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.