Monday Morning Mulling: May 2023 Challenge

On the final Friday of each month, we set an Excel / Power Pivot / Power Query / Power BI problem for you to puzzle over for the weekend.  On the Monday, we publish a solution.  If you think there is an alternative answer, feel free to email us.  We’ll feel free to ignore you.

The challenge this month was to find the longest word from a set of letters.  Did you succeed?

The Challenge

We dedicate this month’s challenge to a certain member of the team who has a penchant for letters and numbers games…

This month, we had a hybrid Power Query and Excel challenge.  We provided a challenge file which includes the following input cells:

The challenge was to accept up to nine [9] input letters, and find the longest word(s) that can be created from them.  Only the longest word(s) should be returned in ‘Results’, and the ‘Score’ will be the length of the longest word.  We have input some letters as an example.  For this selection of letters, the ‘Results’ (for an English dictionary) would be:

You can download the challenge file here.

As always, there were some conditions:

• This is a Power Query challenge and Excel challenge – no VBA is required.
• You may use a free dictionary source of your choice.
• The solution should not be case-sensitive.
• If no words can be found, the solution should indicate this. 

Suggested Solution

You can find our Excel file here which demonstrates our suggested solution.

The solution can be divided into Power Query and Excel sections.  We will use Power Query to prepare the dictionary, and Excel to compare the input letters to the dictionary contents.

We have selected a free dictionary source which we have in a text file.  We enter the location of the text file into cell G10 on the Letters sheet.  Note that this is also identified by a Named Range File_Path.

Using Power Query to Create the Dictionary Matrix

We start by extracting the File_Path information.  We can click inside the cell, and right-click to choose ‘Get Data from Table/Range’:

This takes us to the Power Query editor:

Power Query has generated a ‘Promoted Headers’ and ‘Changed Type’ step: we don’t need these, so we can delete them.  We only need the value, which we can ‘Drill Down’ into:

We now have our File_Path parameter:

Now we need to extract the dictionary data.  We can create a new query by right-clicking in the blank space in the Queries pane:

We select ‘Text/CSV’ from the File dropdown from the ‘New Query’ dropdown, navigate to the location of the dictionary file, and choose to Import.  The dialog that appears will look something like the following image:

Since we are already in the Power Query editor, clicking OK will allow us to transform the data:

If your dictionary has extra columns, then choose the column containing the words and right-click to ‘Remove Other Columns’.  We rename Column1 to Words.  We need to filter out words that have more than nine [9] letters.  On the ‘Add Column’ tab, we can choose to Extract Length from the ‘From Text’ section:

This gives us column Length:

On the filter dialog, we have the option to choose values ‘Less Than Or Equal To…’:

Choosing this option triggers a dialog, where we ‘Keep rows where ‘Length’’ ‘is less than or equal to’ 9. 

Now we have reduced the data, we can remove the Length column.  We access the Format dropdown on the Transform tab and transform the data in Words to lowercase:

Now we have completed the transformations, we need to ensure that the source of this query will change if the user changes the location of the dictionary file.  The ‘Source’ step currently looks something like this:

We need to change this to use the File_Path parameter:

Now the query is complete, we rename it to Dictionary, and take a reference copy:

The query we create will use Dictionary as its source.  We rename it Matrix:

As the name suggests, we are going to create a matrix.  We want to know the occurrence of each letter in each word.  We will show the long version of this, and then a more efficient version.  For the long version, we need to create a ‘Custom Column’ from the ‘Add Column’ tab for each letter:

The M code we have used is:

This creates a list of the positions of each occurrence of ‘a’ in the word, and then counts the values in the list.  We can create similar code for the other columns.

We can then give each step a more sensible name as we go:

This is the rather long result, including a ‘Changed Type’ step at the end to change the data types of all the new columns to a ‘Whole Number’:

We rename this query Matrix Long Version, and take another reference copy of Dictionary, which we call Matrix.  This time. we are going to create all the columns at once, and we can do this by creating a record for each row.  The function we will use, is Table.FromRecords:

If we consider the M code in the ‘Advanced Editor’ (accessible from the Home tab) for Matrix Long Version, we can see that to add each column, Power Query uses the function Table.AddColumn:

Instead of the code we are using here:

In the new query, we want to create a record for the code after ‘each’.

The record will contain all of the columns:

If we combine these, we get:

If we put this in the Advanced Editor for Matrix, we get a column of Tables:

We can now expand the records by using the icon next to the heading of Count of Letters:

We choose not to ‘Use original column name as prefix’ and click ‘OK’:

All the columns have been created in one step.  We choose to set the data type for all the columns to ‘Whole Number’, which we can do from the Transform Tab either by choosing the type, or using the ‘Detect Data Type’ option:

Finally, we choose to ‘Close & Load To’, in order to specify which queries we want to load, and where we want to load them to.  Initially, we choose to ‘Only Create Connection’:

We can then change the setting for the Matrix query by selecting it, and right-clicking to access ‘Load To…’

This time, we choose to load to a Table on an ‘Existing worksheet’ and choose cell G12 on sheet Workings:

The dictionary data is loaded. 

Now we need to compare the input letters with the dictionary.  This will be done with Excel formulae.

Using Excel to Calculate the Matching Words

Once the resultant Table has been loaded to Excel, the first step is to bring in the input set of letters.  We make use of the LOWER function to convert any uppercase letters to lowercase:

We’re going to need to count the number of occurrences of each letter to compare to the matrix generated in Power Query.  Creating headings in row 9 for each of the letters (in our case across 26 columns) will allow us to make use of the following formula:

This will take the input text string, substitute any letters equal to the column heading with blanks (effectively removing them) and then calculate the length of the resulting text string.  The length of this altered text string is subtracted from the length of the input text string, returning the number of occurrences of the letter.

We now need to check if each word in our list can be created using only the letters from the input text string.  To do this we check if the number of occurrences of each letter in the word is less than or equal to the number of occurrences of the letter in our input text string.  We use the following formula starting in cell AH13 as our “a check”:

We multiple by one [1] to convert values of TRUE or FALSE to one or zero [0] respectively.

This check must be performed for each letter in each word:

We will need all of these checks to return true for a word to be valid, so the next step is to build an overall check.  A simple way to perform an AND check here is to make use of the MIN function across this range of checks from “a check” to “z check”:

Now that we have a check that will return a one [1] for valid words and zero [0] for invalid words, let’s calculate the length of each valid word.  We can once again make use of the LEN function, performing it on each word only when the overall check is equal to one [1]:

We will also need to know the length of the longest applicable word.  This can be calculated by taking the maximum value of the column we have just generated through use of the MAX function:

This value will be the ‘Score’, as well as being used in our final check.  We need to check if the length of the word is equal to the maximum, as we only want to display the longest word(s) that can be made.

To do this, we check if the length calculated in the Length column of our table is equal to the maximum length calculated in cell BI10.  As before, this is multiplied by one [1] to return a one [1] in place of TRUE and a zero [0] in place of FALSE.

Finally, we can return to the Letters sheet and produce our results.

We’ll start by bringing the score into cell I10:

Now all that’s left is to bring in the words.  We’ve chosen to make use of one of Excel’s newer functions, FILTER:

First, this formula will check if the ‘Score’ is equal to zero [0].  If the ‘Score’ is equal to zero [0], then “No valid words found” will be returned.  Otherwise, the Words column will be filtered based on the Include column, returning only words where this is equal to one [1]:

Of course, further improvements (such as adding data validation to cell G10) can be performed, but there you have it!  This the real secret as to how Liam got his hands on the coveted tea pot!  [Yeah, right… - Liam]

The Final Friday Fix will return on Friday 30 June 2023 with a new Excel Challenge.  In the meantime, please look out for the Daily Excel Tip on our home page and watch out for a new blog every business working day.