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.    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:

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

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

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)

• Demote the headers to first row
• Transpose the table

And at that point, you have this view:

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:

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:

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!

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!