- 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 | Custom functions exercise | Create and invoke a custom function to extract dates
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 three worksheets from the above workbook, promoting the first row to be the column header for each resulting table:
The tables contain UK valid driving licence numbers.
Your mission is to create a custom function to extract each person's date of birth from their driving licence number, then apply this custom function to each of your tables.
Full instructions for how to do this are shown below (if you're feeling brave you could try without these!).
Start by creating a parameter to hold a typical driving licence number:
Your parameter will hold text. When you've created this, right-click on it and choose Reference to create a query referencing its value.
Right-click on the query referencing your parameter, and begin the process of extracting the person's date of birth:
Start by turning the referenced parameter value into a list.
Turn the resulting list into a table:
If you can find a better way to start this query, use it!
Extract the person's date of birth. For UK driving licence numbers the DD, MM and YY of a person's date of birth appear in the string as XXXXXYMMDDYXX. You can get at the parts you want with a simple split by position:
Use these numbers to get at the columns you want.
After a few easy additional transforms you should be able to get the three things you need:
You'll need to merge the two year columns together, and turn the resulting text back into a number.
If the year is less than 30, we'll assume it's post-millennial, so use a conditional column to add 2000 or 1900 accordingly to the year:
Create a column containing either 1900 or 2000, depending on the value of the year.
Now create a custom column giving the final year:
The custom column should add the two year parts together.
You can now merge the day, month and year together, using a / as a separator:
Having done this, you should be able to convert the results back into a date.
Finally, you should have derived this person's date of birth!
The final date of birth for our example licence number.
You now want to turn this sequence of steps into a custom function (call it GetBirthDate):
Right-click on the query you've just created, and turn it into a custom function.
You should now be able to invoke your custom function to show a date of birth column for each of the 3 imported tables of driving licence numbers:
Click on each of the three tables, starting with A-G, and invoke the custom function you've just created.
As a bonus, you could merge the results of the three tables together:
Choose this transform, and choose to append 3 or more queries in the dialog box which appears.
You should now be able to see the oldest people represented:
Apply sorting to get these results.
This is a training exercise: it would clearly be better to combine the data when you merge it, which would remove the need for a separate custom function!
When you've got this all working, save this report as Have Wise Owl hacked the DVLA and close it down.