Wow… 46 submissions for Power Query Challenge 3!  Crazy stuff. So many that I haven't even had time to read them all yet!

Due to the overwhelming responses, here's how I'm going to handle this:

1. Show you how I approached this challenge
2. Call out a couple of the submissions that I thought were cool and/or interesting
3. Leave it to you to throw a note in the comments if you think that your (or someone else's) submission should have been listed.

You can find access to all of the submitted solutions (including mine) stored on my OneDrive account.

My solution to Power Query Challenge 3

Step 1 of Challenge 3: Assigning scores to letters

The most important component to Challenge 3 is to have a table that assigns a value to each letter, and there are a couple of ways you could do this.  I elected to do this via the following method:

• Create a new blank query and name it LetterValue
• Enter the following formula in the formula bar:
  • = {"A".."Z"}

This creates a nice list of letters from capital A through capital Z:

As great as this is, I still need to add values to the numbers (1 for A, 2 for B, etc..).  It's easy to do with an Index column, but the problem is that the button to do so is not available when you're working with a list.  No problem though, we just convert it to a table first:

• Go to List Tools --> Transform --> To Table --> OK
• Go to Add Column --> Index Column --> From 1
• Rename the columns to Letter and Value
• Set the Data Types to Text and Value

And that's it.  The query (as shown below) can now be loaded as a connection only for later use:

Other options for Step 1 of Challenge 3

To be fair, I'm pretty comfortable knocking out a quick list, as I do it all the time for Calendar tables.  But as Bill Syzyz pointed out, you get bonus points for this being 100% UI driven.  Could I have done that?  Of course!  Instead of creating the list, you would:

• Create an Excel table that holds the letters and values
• Pull it into Power Query
• Right click the Letter column --> Transform --> Upper Case
• Right click the Letter column --> Remove Duplicates
• Load it as connection only

Is it easier?  Maybe.  It it more robust?  It could be more robust if you also want to score other characters.

Step 2 of Challenge 3: Scoring Words

So now we get into the money part of Challenge 3 - scoring the entire word.  Let's assume that we have a nice little Excel table which stores all the words in a column called "Word" like this:

(To be fair, the data could come from a database or anywhere else, the source is really incidental to the problem.)

To score these words we can

• Pull the data into Power Query
• Right click the [Word] column --> Duplicate
• Right click the [Word - Copy] column --> Transform --> UPPERCASE

This leaves us here:

Now the trick…(psst… I have a cool pattern card for this… watch this space in the next few days for news!)

• Right click [Word - Copy] --> Split Column --> By Number of Characters
  • Choose to split by 1 character, repeatedly
  • From the Advanced Options, choose Rows

Your output should now look like this:

Next:

• Go to Home --> Merge Queries
• Choose LetterValue and merge [Word - Copy] against [Letter]
• Expand only the [Value] column from the [LetterValue] column (by clicking the expand icon at the top right of the column)

The data is out of order now, but it doesn't matter.  It's time to put the finishing touches on Challenge 3…

• To to Transform --> Group By
• Configure a basic grouping like this:
  • Group by Word
  • Create a Score column that SUMs the [Value] column

And you're done!

You can now throw any value into the Excel table, hit refresh, and it will score the words…

Well how about that…

• M is for Data Monkey scores 189
• Power Query Academy scores 215

And our upcoming Power Query Recipe cards look like they could be way better than hard work too!  (Stay tuned for their release next week!)

Your Challenge 3 Submissions

With 46 submissions for Power Query Challenge 3, it was impossible to go through them all.  I'm hoping that you'll consider being part of the community of reviewers here and check out a few random ones, then post in the comments any that you think I should have mentioned.  I can tell you that in the first four submissions alone there are three different versions of custom functions, and a UI driven approach.

But there are a couple of submissions I looked at that I did want to highlight, as they brought more than just a query to the game. 

Bart Titulaer

Bart's solution includes not only his Power Query work (which he tried more than one way), but he also thought it might be fun to include some frequency distributions with the solution:

It's a good reminder that the end goal of Power Query isn't just to clean up data, it's to actually use it.  (Something I probably don't cover enough on this blog!)

Kolyu Minevski

Kolyu decided to compare scoring English vs Bulgarian, and even summed it up for me with a note:

Nice to know since I'll be back in Sofia at Bulgaria Excel days on November 1!