- Access exercises (91)
- C# exercises (79)
- Excel exercises (278)
- Power Apps exercises (13)
- Power Automate exercises (18)
- Power BI exercises (139)
- Python exercises (28)
- Report Builder exercises (141)
- SQL exercises (198)
- SSAS exercises (51)
- SSIS exercises (46)
- SSRS exercises (99)
- VBA exercises (85)
- Visual Basic exercises (46)
POWER BI EXERCISES▼
POWER BI EXERCISES▼
- Basic reports (10)
- Data sources (2)
- Query editor (8)
- Filtering data (9)
- Drill-through (1)
- Charts (10)
- Matrices (1)
- Basic maps (6)
- Calculated columns (8)
- Roles and security (1)
- Drill-through and bookmarks (3)
- Report themes (2)
- Power BI mobile (1)
- Advanced tables (3)
- Conditional formatting (5)
- Analytics (2)
- Decomposition tree (1)
- Other visuals (1)
- Tooltips (2)
- Bookmarks (4)
- Quick measures (3)
- Dynamic display (1)
- Custom visuals (3)
- Advanced data sources (4)
- Basic parameters (1)
- Column transforms (2)
- Combining queries (2)
- Manipulation transforms (1)
- Normalising tables (2)
- Simple parameters (1)
- Dropdown parameters (1)
- Stored procedures (1)
- Dynamic connections (2)
- Custom functions (3)
- APIs (3)
- Power BI Templates (1)
Power BI | Column transforms exercise | Make sense of gun ownership statistics
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 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.
Load the table of gun ownership data from the workbook in the above folder. The original source for this is:
Initially the data doesn't look very promising:
We need to sort out the top rows, and rename some columns.
Skip the top 2 rows, promote the next row to be your header row and rename columns to get:
Better: we have something to work with!
The Ratio column has a red mark below it, showing you have at least one error. The problem is that converting it to a number didn't always work, so remove this step:
Delete this step only (we want to keep the one which renamed the columns).
Sort the Ratio column to see what the problem is (it should be the first or last row, depending on how you sort) and solve this:
Replace the <0.1 with just 0.1 using the Replace Values transform, then change the data type of this column to be a decimal number (this shouldn't now generate any errors).
Sort your data by this Ratio to see which country has the highest gun ownership rate in the world, then add an index column to number your rows (and rename this column as Order):
Oh America! The UK is about number 190 (although those gun-toting Falkland Islanders let the side down a bit).
Create a custom column to show the true ratio between the number of firearms and population for each country (multiply by 100 to get the figure as a percentage):
The custom column formula should just be one figure divided by another.
This should give something like this:
The calculated ratios aren't quite the same as the ones given.
Add another calculated column to show the absolute value of the difference between the two ratios (the one given, and the one you've calculated):
A clue to start you off!
Sort by this to show the countries with the largest discrepancies:
Most of these have tiny numbers involved, but the figures for Saudi Arabia look like a genuine error.
Load your query into Power BI Desktop, then save this workbook as Smug UK and close it down.