Power Query Challenge 3 Results

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:

SNAGHTML99b7af

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:

SNAGHTML9d4b74

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:

image

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

image

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:

SNAGHTMLaa6bfd

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)

SNAGHTMLad0db2

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

image

And you're done!

image

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

image

Well how about that…

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.  Smile

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:

image

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:

image

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

14 thoughts on “Power Query Challenge 3 Results

  1. I was aware you could do sometbing like {2..9} to get a list of numbers from 2 to 9. I never realised you could do the same with characters. Cool!

  2. Thanks for the challenge Ken. I think there were some great submissions from the handful I opened. I hope you expound on some of the more unique solutions more when you have some time.

  3. I had created custom m function pattern to perform this task. I will think i could have combined A..Z in same query itself instead of separate table.

  4. Hi Ken,
    I am glad my submitted solution so closely resembles your suggested one for both the steps!!

  5. 2 Lines of M Code
    let
    Source = Excel.CurrentWorkbook(){[Name="W"]}[Content],
    mAddCustCol = Table.AddColumn(Source, "Score", each List.Sum(List.ReplaceMatchingItems (Text.ToList(Text.Upper([Word])), List.Zip({{"A".."Z"},{1..26}}))))
    in
    mAddCustCol

  6. Hi

    I see that Wyn Hopkins approach is almost identical with my solution.
    The key steps:
    Duplicate the word column.
    Split Column into rows.
    Change words to lower text or upper text.
    Custom column with Character.ToNumber() minus offset depending on lower/upper text.
    Group by Word column and sum the Score column.

  7. @Sam...that's really slick. Even expanding out to a few more steps for readability, that's still probably the most compact way to do it. You can even parameter-ize the 2 lists that feed List.Zip() as tables that are converted to lists, so that it's easier to maintain.

    Thanks for sharing this solution!

  8. Now is after the competition so i can present one-step query (for fun only).
    The other characters than A-Z are possible and are not taken into account during counting.

    let

    Source = Table.AddColumn(Excel.CurrentWorkbook(){[Name="Table1"]}[Content], "Score", each List.Sum(List.Transform(Text.ToList([Word]), each Text.PositionOf("ABCDEFGHIJKLMNOPQRSTUVWXYZ", _, 0, Comparer.OrdinalIgnoreCase) + 1) ) )
    in
    Source

    Of course, instead of "ABC..XYZ" we can use Text.Combine({"A".."Z"})
    If you are interested how it works, check my video on YT
    https://www.youtube.com/watch?v=fNPzdEAyA8c&t=7s

  9. I ran into a couple of issues with my original solution that looked basically like Kens.

    1) If you put in duplicate strings, you get double counted totals.
    2) For some reason, when I did these steps the sort order differed from the original.

    I fixed both by adding an index before doing the split by rows and then using it in the group by step and also to sort the final result.

  10. Hi Chris,

    I can see why it would double count the results, as the word is the same, so it groups them. Using the Index column approach would solve it, as you could choose to group by the numeric value instead, which would be unique.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.