Don't rely on AI

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':

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:

On to example 3:

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:

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:

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:

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:

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

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:

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