Hopefully you’ve had a chance to try and build a solution to Power Query Challenge 5.  In this thread, I’ll show you how I approached it.

Challenge Goals

The challenge in this case was to allocate a fixed amount for a series of account IDs based on the data in this list:

Creating this…

And as a reminder, the rules were:

• 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)

And you can click here to get the original data file (as well as see solutions posted by myself and others).

I would also like to quickly restate that another of my main goals was to do as much using the UI as possible, and make certain that the students in my workshop could maintain this solution.  During the three day workshop (similar to my upcoming self service BI bootcamp), we did a full day of Power Query work, but mainly around reshaping data through the user interface.  The deepest we got into M code was creating a list using a structure like {1..10}.  So moving to crazy conditional logic and List.Generate solutions was out of the scope of where I wanted to take this for them. 😉

One solution to Power Query Challenge 5

I broke my solution down into four distinct queries as follows:

Let’s look at each of those steps.

The Base Query

The point of this query was basically to carve up the “From” and “To” portions into the components that I would need in order to get my solution.  With the preface remaining the same for every ID in the list, it was all about separating the last 3 digits to go from this table:

To this one:

The steps I used were as follows:

• Select the [From] column --> Add Column --> Extract --> First Characters --> 7
• Name the new column “Preface”
• Select the [From] column --> Add Column --> Extract --> Last Characters --> 3
• Name the new column “From3”
• Add a new conditional column called “To3” with the following formula:
  • =if [To] = null then [From3] else Text.End([To],3)
• Remove the [From] and [To] columns
• Set the data types for each column (notice [From3] and [To3] are text, not numbers)
• Load the query as a Connection Only query

The Numeric Query

Next up, I needed to generate the series for the numeric series.  This was easy, as it works similar to the Power Query Calendar table recipe.  (Recipe card 60.105.x which you can find in our Power Query Recipe Cards)

The steps I used do this were:

• Reference the Base query
• Change the type of the [From3] column to Whole Number
• Filter the [From3] column to remove errors
• Added a new Custom Column with the following formula:
  • ={ [From3]..[To3] }
• Removed the [From3] and [To3] columns
• Expanded the new [Custom] column
• Merged the [Preface] and [Custom] columns into a new “ID” column
• Loaded this query as Connection only

This left me with the query output shown here:

And the cool this is that using this technique, the final series – which only had a “From” ID and no “To” ID just creates the single item

The Alpha Query

Next up was the alpha query.  Once again, I referenced the Base query, meaning that I started from here:

The steps I followed here were:

• Duplicate the [From3] column
• Change the data type on the [From3 – Copy] column to Whole Number
• Filtered the [From3 – Copy] column to KEEP errors
• Removed the [From3 – Copy] column
• Split the [From3] column by Number of Characters, splitting at the left-most 2 characters
• Split the [To3] column by Number of Characters, splitting at the left-most 2 characters
• Added a new Custom Column with the following formula
  • = { [From3.2]..[To3.2] }

So at this point, I’m looking at this:

Continuing on, I…

• Removed columns [From 3.2], [To 3.1] and [To 3.2]
• Expanded the list from the [Custom] column
• Merged the [Preface] , [From3.1] and [Custom] columns into a new “ID” column
• Loaded this query as Connection only

And these steps left me with a nice list of the alphanumeric series as follows:

The IDs Query

At this point, things became super simple.  There’s no real magic to this query, as the data has already been prepared and normalized in the Numeric and Alpha queries.  So all that needs to be done here is:

• Reference the Numeric query
• Append the Alpha query
• Set the data types (just to be sure)
• Load the query to the intended destination

And that’s it.

That’s One Solution to Power Query Challenge 5

Of course, there are countless other ways to solve this.  My goal in this case was specifically to avoid a custom function, as I didn’t want to obfuscate the code for a new Power Query user.  Could I have done this all in one query with use of more complicated Custom Columns?  Sure.  But again, would it be maintainable by people newly introduced to Power Query?  Hopefully intellisense in the Custom Column Dialog will change my answer, but right now I’d say that’s unlikely.

One of the things I love about Power Query is the ability to break this down into separate queries, then put them back together.  The reality is that this series of data was mixed, needing slightly different treatment in each case.  This setup allowed me to clearly focus on the goal, getting it done without noise from the other type of data in the set, then just append them afterwards.  I call that a win as it’s easily auditable by someone closer to the beginner side of the Power Query learning curve.