Power BI | Custom functions exercise | Create and invoke a custom function to extract dates

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. 

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.

When you've got this all working, save this report as Have Wise Owl hacked the DVLA and close it down.