Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
547 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Software ==> | Power BI (160 exercises) |
Topic ==> | Custom functions (3 exercises) |
Level ==> | Relatively easy |
Subject ==> | Power BI training |
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.
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.
You can find other training resources for the subject of this exercise here:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.