ICAEW.com works better with JavaScript enabled.
Exclusive

Don't rely on AI

Author: Simon Hurst

Published: 18 Dec 2020

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

For the avoidance of doubt, that's AI as in Artificial Intelligence rather than a deliberate slur on your mate Al down the pub.

Some years ago, many news outlets took great delight in reporting the journey that a couple of friends had made to Stamford Bridge. They had entrusted the navigation duties to their SatNav and, instead of travelling a few miles to see Chelsea football club suffer a humiliating defeat (the removal of the commenting option from community posts does have some advantages), they ended up several hundred miles away at a place where Harald Hardrada had suffered a humiliating defeat over 900 years previously.

The obvious lesson was the need to temper reliance on the wonders of technology with an element of common sense and scepticism. I was reminded of the importance of this vital life skill a couple of days ago when I was constructing an example that demonstrated how Power Query can 'learn' from the examples you give it what you want to do and then implement a suitable solution.

My contrived example was originally designed to show how Power Query's 'Column From Examples' feature could be taught to construct a simple formula to extract the first 2 characters from a text string. However, I thought that I would give Column From Examples the chance to do something even more impressive and convert a US format date into a proper UK date.

Here is our data in the Power Query editor. We have selected our Date column which is currently recognised as containing text values and then, from the Add Column Ribbon tab, clicked on the Column From Examples dropdown and chosen 'From selection' in order to restrict Power Query to just considering the Date column for its proposed transformation. We have then given our new column the name 'Proper date' and typed in the UK equivalent of the first date. This doesn't give Column From Examples enough of an example, so it entreats us to 'Please enter more sample values':

Excel screenshot

After a further example, Power Query comes up with a proposed solution, but examining the other results show that it certainly doesn't achieve what we need:

Excel screenshot

On to example 3:

Excel screenshot

Now, at last, Power Query seems to have got the message and we can click on the OK button to accept our new column with the formula proposed:

Excel screenshot

We can see our full, and rather formidable, formula in the formula bar.

Our first check should be to change our data type from text to a date by clicking on the icon at the left of the column header. Our text column is converted to a column of UK dates with no apparent errors.

Just before embarking on a victory lap around the office we might want to pause and think a bit about our solution. Although it is sufficiently long and complicated to make the way that it works far from obvious, the fact that it relies on numbers of characters without ever identifying the / as a delimiter might be a cause for concern. Also, if we check on our original Date column, it has no two-digit months. So, at the very least, we might want to see how our formula copes with a fuller range of date possibilities:

Excel screenshot

We have added three rows to our original table with two-digit dates, and we can see that our Power Query solution isn't quite as brilliant as it might have seemed at first. The three two-digit dates all generate errors. In fact, even without worrying about October, November and December, if someone entered single-digit months or days with a leading 0, these would also generate errors.

If we edit our query, and go back to the step before we changed our new column data type to a date, we can see that our formula completely fails to cope with different numbers of digits for the month:

Excel screenshot

So, we'll have another go by deleting all our steps from our Added Custom Column step until the end and using Add Column, Column From Examples, From Selection again. This time, we have deliberately started typing our examples on row 12 so that they straddle the one-digit, two-digit entries. After three examples Power Query comes up with what looks like a solution:

Excel screenshot

Here is what our data, and our formula, looks like now:

Excel screenshot

We can see that, this time, our formula does include the use of the SplitTextByDelimiter() function using the /.

Two substantial learning points remain.

The first is the need to make sure that the data that we use to 'train' Column From Examples includes all the possible variations that the resulting formula might need to cope with. For example:

  • 1/1/16
  • 01/1/16
  • 1/01/16
  • 01/01/16
  • 01/01/2016
  • 1/1/2016
  • 1/12/16
  • 12/1/16
  • 1/13/16

Even our second proposed formula has not yet been tested with a four-digit year, for example.

Secondly, although AI might generate a viable solution, it might also result in a vastly more complicated and cumbersome formula than a more informed alternative. In this particular case, we could just have chosen to change the data type of the existing Date column using the 'Using Locale…' option and specifying that the dates originate from English (United States). Here we have applied the data type solution and moved our columns so that we can compare the two results more easily:

Excel screenshot

As you can see, the manually created step is a bit more straightforward than the AI alternative.

Excel community

This article is brought to you by the Excel Community where you can find additional extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business.

Excel polaroid
Topics
Open AddCPD icon

Add Verified CPD Activity

Introducing AddCPD, a new way to record your CPD activities!

Log in to start using the AddCPD tool. Available only to ICAEW members.

Add this page to your CPD activity

Step 1 of 3
Download recorded
Download not recorded

Please download the related document if you wish to add this activity to your record

What time are you claiming for this activity?
Mandatory fields

Add this page to your CPD activity

Step 2 of 3
Mandatory field

Add activity to my record

Step 3 of 3
Mandatory field

Activity added

An error has occurred
Please try again

If the problem persists please contact our helpline on +44 (0)1908 248 250