Power Query Challenge 3

**Please note that the challenge is now closed, so we are no longer accepting submissions. However, you are still welcome to try it out on your own.

I've got a lot of feedback that you enjoy the Power Query Challenge series we've been running, so it's time for another!  Challenge 3 is just a fun one that was inspired by a conversation I had with Alex J.

Background for Challenge 3

I'm sure you've all seen this before:

If the letters A-Z are worth a value of 1-26 respectively, then:

  • Knowledge = 96%
  • Hardwork = 98%

Of course, the joke is that Attitude is worth 100%.

Your job for Challenge 3

For challenge 3, based on assigning a letter score of 1 for A, 2 for B, 3 for C, etc…:

  • Take a table of words
  • Return the total score using Power Query

So basically… do this:

image

But keep in mind that correctly solving Power Query Challenge 3 requires one very important thing… It needs to work no matter the case of the letters in the original column.

Before you submit your answers to Power Query Challenge 3

After our previous challenges, I got feedback that people really want to see the solutions submitted by others.  I haven't shared them only because I didn't specifically say I was going to, and I can't guarantee that there isn't personally identifiable information in them.  That will change here…

Submitting Your Answer to the Power Query Challenge

**The challenge is now closed, so we are no longer accepting submissions.

To submit your answer:

  • Please name your file using your name - or the name alter-ego if you prefer.  (Keep in mind that your real name could be in the file properties, and it's up to you to clean that out.)  The reason we need a name is so that anyone reading solutions can ask questions about your solution.
  • We have to cap this somewhere, so will allow submissions until the end of Sunday, Sep 16 only.
  • While the challenge is open, you can submit your answer to Rebekah at Excelguru dot ca with the subject Power Query Challenge 3.

After Submissions are closed:

I will post my solution to Power Query Challenge 3, and share a link to a folder of all the submitted solutions.

Please note that last challenge we had over 30 submissions.  While I may post some highlights from the solutions, I won't be doing a full write-up of all them due to the volume we expect to see.

Have fun!

12 thoughts on “Power Query Challenge 3

  1. Hi Ken.. Submitted My Challenge Solution & Waiting for the results 🙂
    it was amazing and nice trick to solve it..this is super cool challenge.. Just love solving it.

  2. Additional points for participants who will do this through UI (without writing any code)
    Or.... for those who will write a one-step query. ;-))))
    Joke, of course, but both of the above are possible

  3. JB, I think that depends on who will be maintaining it, and your comfort with code. Personally, I'd rather have a few more steps to make it easier to digest, unless there is a major performance impact.

  4. Hi Ken, thanks for the challenge. At first I had no Idea how to solve this but step by step I got small insights. I also made a freqency table of all letters. My solution ignored non alphabet codes like & @ *, but I did nothing special to that. 🙂

  5. @Sam, really curious to see your 2 line solution in a few weeks.

    @Ken, this was a fun one! It definitely pushed me and I learned a few new tricks along the way. Thanks for coming up with these!

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.