One Solution to Power Query Challenge 5

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:

Can you make a series from one to the other?

Can you make a series from one to the other?

Creating this…

Desired Power Query Output

Here is the data we need to create

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:

Listing of all queries used

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:

Can you make a series from one to the other?

To this one:

Adding From and To columns

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:

Creating the Numeric Series

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:

Adding From and To columns

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:

Creating a text based list

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:

Creating the Alphanumeric Series

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.

Power Query Challenge 5

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?

Can you make a series from one to the other?

And the required output:

Desired Power Query 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!

Self Service BI with Excel and Power BI

This week Ken is at the Microsoft Business Applications Summit in Atlanta. However, we're super excited that he'll be back on home turf in July for our next public training session. The 3-day Self Service BI Boot Camp in Vancouver will change your company's reporting game forever!

Is Self Service BI a Double-Edged Sword?

Matthew Roche, a member of Microsoft's Power BI CAT Team and noted sword enthusiast, recently posted about a trend he's seeing. His global customers often describe self service BI as a "double-edged sword." After thinking about this comparison, he felt the simile held up. But perhaps not in the way you think!

In the post on his blog, Matthew summed it up nicely. Having two sharp edges is only dangerous to the wielder if they don't know how to effectively use each of the distinct and complementary edges together.

Approaching Self Service BI for the First Time

Perhaps you've been thinking about implementing some self service BI solutions within your organization. And you've been hearing a lot of buzz about Power BI. But you're unsure where to start or how Power BI fits in with your existing data platforms and reporting tools.

Our Self Service BI Boot Camp, with 3 full days of immersive hands-on learning, might be just the place to begin. Guided by the Excelguru, Ken Puls, you'll work with both Excel and Power BI to build some really cool self service BI solutions! We'll not only show you which tool is right for which kind of job, but how to use them together in one solution.

What will the Boot Camp Cover?

We know that data is not usually stored in nicely curated databases and often - even when it is - the data analyst doesn’t have access to it. Instead, we must piece together data provided in text files, Excel files, web pages, and even the body of emails to build the business intelligence solutions we need. Until now this has been a painful process with a great deal of cut and paste effort, and updates have been tedious and error prone. That stops with this workshop!

Building Modern BI Solutions

In the Self-Service BI Boot Camp we'll dive into:

  • a deep exploration of Power Query for gathering, cleaning, and preparing your ugly data
  • the benefits, concepts, and key terminology of Dimensional Modeling
  • an introduction to DAX,including:
    • how to create a variety of DAX measures
    • how to control their Filter Context
    • understanding how DAX measures are calculated

Learn how CALCULATE works in our Self Service BI Boot Camp

  • calendar intelligence, such as:
    • building calendar tables
    • using the “Golden Date” pattern
    • extending our model to report based on our own year-end
  • how to leverage the strengths of Power BI for sharing and reporting
  • specific features of Excel and Power BI that every analyst should know, plus:
    • which tool to use for which job
    • how they can be used together
  • recommended best practices for boosting the performance of your self service BI solution

You can read more about the class on the Excelguru website. Likewise, check out the course outline to see what will be covered in each of the 3 days.

Top 5 Reasons to Attend

  1. Our Self Service BI Boot Camp is loaded with hands-on, practical experience. As Ken's friend, Thomas LaRock, recently commented on Twitter, "Telling isn't teaching." Our philosophy is that you need to actually work with a tool in order to really learn it.
  2. The small class size provides a more intimate learning environment. Working with a small group allows Ken to interact directly with attendees, answering your questions and fielding discussions as they come up. You get to pose questions and discuss scenarios with one of the world's leading self service BI experts. However, this means that spots are limited, so don't  miss out on your chance to sign up.
  3. There's no better time to come to Vancouver than in July - it's a beautiful city all year round, but is in its prime this time of year. That being said, while we can't 100% guarantee great weather, we CAN guarantee you'll get some GREAT training!
  4. Early registration pricing is now in effect. You can save $200 if you sign up before June 24, 2019. Simply go to our website and enter the following coupon code at checkout: SSBIJULY2019.
  5. Registration includes some valuable resources to take with you, so you can refer to them later. You'll get a copy of Ken's slides, many of which contain handy recipes to walk you step-by-step through the techniques. Additionally, youreceive copies of all the class example files, including completed versions for later review. These examples are based on real-world scenarios and provide you with techniques that you can being applying to your own data right away.

Unpivot Subcategorized Tables with Power Query

So what are you waiting for? Come and join us to revolutionize your reporting process!

 

Creating Two-Tiered, Multi-Frequency Period Cycles

Sometimes, what should be easy code to write has unexpected pitfalls resulting in opportunities for new learning. I needed to generate a one or two-tiered stream of date values with multi-frequency period cycles, monthly vs annual.

For example, after installing a piece of equipment I wanted to schedule 12 monthly calibrations, beginning with the month after installation, followed by 3 annual calibrations, beginning 6 months after the last monthly calibration.

This is a table of schedule parameters that defines a few sample scenarios:

Example table outlining desired multi-frequency period cycles

Create a Simple List using List.Generate()

My plan was to create generic schedule scenarios, based on specific types of equipment and use period parameters relative to the installation month.

My first thought was to use Power Query’s List.Dates() function to create the sequence of dates, but List.Dates() only increments by combinations of days/hours/minutes/seconds. Not helpful when you need to increment by months. Consequently, I turned to the List.Generate() function to generate the list of periods.

First I wrote some M-Code to test the concept using hard-coded parameters:

Query: JustCreateList
M Code:

M code for JustCreateList

And the results were this:

Results for JustCreateList query

Great! That was easy. If I can make a list and add it to each row of a table, then I can expand each list and build cycle dates.

Use List Values to Create Dates

Query: BuildDates_HardcodedParams
M-Code:

M code for BuildDates_HardcodedParams

And the results are:

Results for BuildDates_HardcodedParams query

So far, so good. (I may leave early today.)

Use Column Values as List.Generate() Parameters

Next, let’s use column values to drive the Lists. What could be easier, right? Here’s one of my many failed attempts:

Query: BuildDates_ColValParams_V1
M-Code:

M code for BuildDates_ColValParams_V1

And the results are:

Error generated by BuildDates_ColValParams_V1

Wait! What??? Where are my lists? And, what “field access”?

I Discover “Internal Context” and “External Context”

I won’t go into all of the iterations I tried to get that darn code to work. Suffice it to say that I tried every conceivable variation of “this”, “each”, “_” and anything else I could think of. All to no avail… Same error message.

Now, I could have built a separate function (which I did, and it worked):

Query: fnCreateList
M-Code:

M code for fnCreateList

I also could have embedded a function within the M-Code (which I did, and it worked):

Query: SingleTier_EmbeddedFunction
M-Code:

M code for SingleTier_EmbeddedFunction

BUT, I wanted a concise, elegant solution. I wanted to just put the List.Generate() function in my code and reference the table columns, just like I would with an Excel function (which I did and…well…you saw the results). I needed help.

So, I posted on Ken’s forum. And who do you think volunteered to assist me? Miguel! (Thank you, Miguel.) Once he established that my interest in referencing column values in List.Generate() had gone way past idle curiosity and quest…all the way to “mission from god”, he gave me a working example and a concise explanation.

Handling Internal and External Context

Because the List.Generate() function is essentially recursive, it has to look at its own sequential results and test them against its internal limits. It needed explicit code to indicate when it should use external context (table references) and when it should use internal context (intermediate function results). I won’t pretend that I understand the “why” of the required syntax, but I quickly picked up on the “how”.

Here’s the working M-Code that uses column values in the List.Generate() function to create the first date sequence:

Query: SingleTier_EmbeddedInnerContext
M-Code:

M code for SingleTier_EmbeddedInnerContext highlighting (listval)

By simply creating a dummy parameter (listval), the List.Generate() function automatically populated it with its own sequential results and it understood that the column references pointed to the Table! I could have named that parameter “moonchild” or “Sue” or anything else. All that mattered is that it had something to populate.

Now, my results were valid:

Results for SingleTier_EmbeddedInnerContext query

Over the first major hurdle and it was a BIG one!

Combining Lists to Generate Two-Tiered Multi-Frequency Period Cycles

The rest of the solution was relatively easy after that. I needed to allow for a second tier of cycle dates.The basic steps were:

  1. If Tier1 begins after the Install Date, make a placeholder for the Install Date… a period zero.
  2. Create the Tier1 date sequence.
  3. If there’s a Tier2 sequence, create it.
  4. Sequentially append the constructed cycle date sequences.

Since that involves M-Code not covered in this blog post, I’ll just post the final solution:

Query: TwoTier_EmbeddedInnerContext
M-Code:

M code for TwoTier_EmbeddedInnerContext highlighted (listval)

And here are some of the results of my table containing the desired two-tiered, multi-frequency period cycles:

Final table displaying our multi-frequency period cycles

Power Query/Get and Transform has steered my career into new, creative, challenging, and rewarding directions. I’d like to thank Ken for giving me the opportunity to share what I learned about internal/external context and my learning process. I welcome any and all questions and comments.

-Ron Coderre