It’s been a while since our last challenge, so hopefully you’re ready for Power Query Challenge 5!  This is one that came up as I was teaching one of our 3 day Excel BI Bootcamp courses for a client, (similar to this public course!) which actually made it doubly tricky.  Why?  Because I needed the person to be able to both understand how I approached the task, as well as be able to maintain it after I left.  I didn’t want to just kick up some magic voodoo and walk away, as that isn’t really fair.

The issue for Power Query Challenge 5

The challenge in this case was to allocate a fixed amount for a series of account IDs.  They provided a starting ID, an ending ID, and a value.  Sounds easy so far right?  Here’s the problem… the key part of some of those IDs were purely numeric, but the key part of others were alphanumeric!  An example of the source data:

Can you make a series from one to the other?

And the required output:

Here is the data we need to create

The rules – as they were explained to me – were as follows:

• The first 7 digits won’t change for the series
• If there is no “To” value, then we only need the single value
• If the final character is text, it will never exceed Z (i.e. we’ll never go from 10A to 11C)

How would you solve Power Query Challenge 5?

My first thought was that I’d have to reach to a custom function to do this, but again, I didn’t feel that was fair to my audience with only a day of Power Query training under their belt.  (With Dimensional Modeling and DAX to cover in our workshop, we don’t have time to take people deep into custom functions.)  After playing with it for a bit though, I was able to work past that problem and come up with a UI driven solution which works.  (Having said that, how you decide to solve this is really up to you!)

Now last time, I posted my solution the same day, along with my steps.  The result was that we had less engagement, possibly because that gave too much away.  So this time, I’ve created the thread for you to post your solutions in the forum, but I’m going to hold off on posting my approach until tomorrow.

Good luck!