Power Query Challenge #2 Results

What an overwhelming response to Power Query Challenge #2!  We had 40 submissions, and some with multiple entries in a single submission.  Plainly you all enjoyed this!

Naturally, there were a couple of submissions that involved custom functions, and a couple who wrote manual grouping functions to get things done.  These folks obviously know how M works, so I'm going to focus more on the other entries to show different UI-driven routes to accomplish the goal.  Winking smile  Each of those is included in the workbook that you can download here.

The Base Query

I'm going to start this by creating a base query called "Source Data" which has only 2 steps:

  • Connect to the Data Table
  • Set the data types

This is going to make it easy to demo certain things, but also replicates what a lot of you did anyway.

Most Popular Solutions to Power Query Challenge #2

By far the most popular solution to Power Query Challenge #2 was by starting using one of the following two methods:

Method 1A - Group & Merge

  • Reference the Source Data query
  • Merge Customer & Membership
  • Remove duplicates on the merged column
  • Group by the Customer column and add a Count of Rows

Method 1B - Group & Merge

  • Reference the Source Data query
  • Remove all columns except Customer & Membership
  • Group by the Customer column and add a Count of Distinct Rows

Either of these methods would leave you with something similar to this:

image

Method 1 Completion

No matter which way you sliced the first part, you would then do this to finish it off:

  • Filter the Count column to values greater than 1
  • Merge the filtered table against the original data set:
    • Matching the Customer column
    • Using an Inner join
  • Remove all columns except the new column of tables

image

  • Expand all columns
  • Set the data type of the Data column and you're good

image

Of the 34 entries, this variation showed up in at least 25 of them.  Sometimes it was all done in a single query (referencing prior steps), sometimes in 3 queries, and sometimes it wasn't quite as efficiently done, but ultimately this was the main approach.

A Unique Solution to Power Query Challenge #2

I only had one person submit this solution to Power Query Challenge #2.  Given that it is 100% user interface driven and shows something different, I wanted to show it as well.  I've labelled this one as Pivot & Merge.

Here's the steps:

  • Reference the Source Data query
  • Remove all columns except Customer & Membership
  • Select both columns --> Remove Duplicates
  • Pivot the Customer column (to get of products by customer)

image

  • Demote the headers to first row
  • Transpose the table

And at that point, you have this view:

image

Look familiar?  You can now finish this one using the steps in "Method 1 Completion" above.

Personally, I don't think I'd go this route, only because the Pivot/Transpose could be costly with large amounts of data.  (To be fair, I haven't tested any of these solutions with big data.)  But it is cool so see that there are multiple ways to approach this.

The Double Grouping Solution to Power Query Challenge #2

This is the solution that I cooked up originally, and is actually why I threw this challenge out.  I was curious how many people would come up with this, and only a couple of people put this out there.  So here's how it works:

  • Reference the Source Data query
  • Stage 1 grouping:
    • Group the data by Customer and Membership
    • Add a column called "Transactions" using the All Rows operation

This leaves you here:

image

Now, you immediately group it again using a different configuration:

  • Group by Customer
  • Add columns as follows:
    • "Products" using the Count Distinct Rows operation
    • "Data" using the All Rows operation

Which leaves you at this stage:

image

It's now similar to what you've seen above, but we have a nested table that contains our original data.  To finish this off, we now need to do this:

  • Filter Products to Greater than 1
  • Expand only the Transactions column from the Data column
  • Right click the Transactions column --> Remove Other Columns
  • Expand all fields from the Transactions column
  • Set the data types for all the columns

And you're there!

image

Final Thoughts

Again, there were more solutions submitted for Power Query Challenge #2.  We had:

  • A couple of custom function submissions (of which each was slightly different)
  • A couple of custom grouping solutions (not written through the UI)
  • A couple of solutions that used grouping, then used a custom column to create a table based on the grouped output which filtered to distinct items

If I haven't covered yours here and you feel that I missed something important, please drop it in the comments below!

The part that fascinates me most about this is that we had UI driven submissions involving merging, transposing and grouping.  Three different methods to get into the same end result.

Thanks for the submissions everyone!

9 thoughts on “Power Query Challenge #2 Results

  1. Ken,
    Great fun with Challenge #2, hope there's more to come in future. As a "UI Double Grouper", I was pleased that my team won the challenge! (Oops - no prize).

    Seriously though, I would be interested to look at the non-UI solutions as a means to improve my understanding if M language. Hope those can be made available.

    Also, I'm happy to see the uptake on this technology. I still get too many blank looks when I mention Power Query, like people think I'm an Excel geek or something. 😉

    Thanks again

  2. LOL!

    So on the non-UI solutions, that's a fair comment. Give me a few days and I'll get permission from everyone to share their files. Then I can add them to the folder.

  3. This was a fun challenge. Now, here's something to consider.
    Since we were given a Customer ID, I approached this as if the Customer names might be the same. So, I worked based on the ID, not the names. Thus, when Customer ID 8 named Claire signs up for the Marina, NEITHER 8-Claire nor 4-Claire should be in the result.

  4. Hi Ken 🙂
    Unfortunately, I missed up this contest :-(((
    But i would like to compensate my absent by this one-step Query ;-))) (for fun only)
    let
    Source = Table.Sort(Table.Combine(Table.SelectRows(Table.Group(#"Source Data", {"Customer ID"}, {{"CountDistinct", each Table.RowCount(Table.Distinct(_[[Customer ID],[Membership]])), type number}, {"All", each _, type table}}), each [CountDistinct] > 1)[All]), {"Customer ID", "Date"})
    in
    Source
    Thanks for contest :-)))

  5. Hi Ken.. it was great solving this challenge#2, we need more such challenges every month.. I have use customer ID to group the data & then filler the memberships greater than one and Finally merge the output with the source data 😉

    One thing I have done I have added index numbers to sort the position of the each entry...
    thanks...

  6. That was great fun, thanks for posting this Ken! I ended up with the double grouping solution, like you did. It would be great to see some more of the custom functions and how others solved this challenge.

    Look forward to that!
    Rick

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.