**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:
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!
20 thoughts on “Power Query Challenge 3”
What kind of sadistic challenge is this? Are you trying to make me sad?
Why would that make you sad, buddy? Believe it or not, "Excel on Fire" scores the same points as Excelguru. 🙂
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.
Submitted my solution. Looking forward to see all the other solutions.
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
But isn't it more clear to have dedicated step to make it more readable ?
Submited by the way 😉
2 Lines of M Code - Done
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.
Done, Submitted and still looking at other ways to skin the cat before the deadline date
hola Ken quiero enviar mi solucion pero no se a donde, me puedes ayudar.gracias.
sls
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. 🙂
@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!
Down to 4 rows. Looking forward to seeing smaller solutions
It would be nice to see a hard challenge for #4. Perhaps two categories: 1) an easy category that can be done through the GUI; and 2) something that requires very deep M knowledge.
@simon, I'm not sure I want to run a side by side challenge, as it's double the work. I could certainly do an Advanced challenge though... Curious here as to your thoughts on this though: I have had someone submit something to me that I have not had time to dig in to and answer. I've never posted a challenge where I haven't worked through it, but is that something you'd be interested in trying out? I.e. As a reader, would you accept a challenge where I have not (and may not have time to) develop a solution of my own?
This took a couple of days for me to think about. And I realized I was thinking too hard, and trying to resist a solution that would be embarrassingly simple and convoluted. So, I went with the embarrassingly simple and convoluted:
-- redacted --
It works. And if we want to start using characters like ê, ã, or Ç, just stick them into the lookup table.
Hey Oz,
Thanks for playing. I've removed your steps for right now to keep the challenge alive for the folks that want to play at home. Will post a solution post soon though!
Oh, no! I missed this challenge!... 🙁
Oh. Ok. No problem. I'm eager to see what people came up with.
No worries, we will be posting more of them in the future so keep an eye out for them! Cheers, Rebekah