Power Query Challenge #2

I'm at the Microsoft Business Application Summit this week, so I thought I'd post another Power Query challenge, especially since our last one was so successful.

For this Power Query challenge …

Our business challenge here is that we are in the process of working out how to reward customers that buy memberships in multiple business areas across our organization.  To perform this analysis, we have been provided a list of transactions that looks like this:


What we've been asked to generate here is a list of all the transactions pertaining to customers who have purchased from multiple business units.  In other words, we want this output:


There's a couple of pieces we need to watch for here:

  1. Susan and Bob are the only people in this list who bought memberships to multiple business units.
  2. Susan bought multiple Golf Course memberships (one for her and one for her spouse).  We need to keep both those transactions - even though they are in the same division - as she also bought a Marina and Fitness Club membership.
  3. Claire also bought two Golf Course memberships.  While she bought multiple products, they are from the same business area, so we want to ignore them.

What are the rules for Power Query challenge #2?

It's pretty simple really.  This is a Power Query challenge.  That means you can use Power Query in Excel, or Power Query in Power BI.  VBA, SQL and Excel formulas results don't count.  Winking smile

I've got a Power Query driven solution all cooked up to return the results above.  And now I'm curious to see how you would solve this problem using the same tool.

Ready to give this Power Query challenge a try?

Like we did with our last Power Query challenge, we're going to ask you: Please Do NOT post your answer below.  (We don't want to spoil it for anyone who wants to play along.)

Please note: the submission period for the Challenge is now closed. The submissions are being reviewed and will be discussed next week!

You can download the source data for this Power Query challenge here.  Have fun!  Smile



13 thoughts on “Power Query Challenge #2

  1. Done! Though my first try I ran into an issue I do not understand, so I'll post about that next week once the answer is revealed.

  2. Got my solution as well! Looking forward to seeing the various answers : ).

  3. hola Ken, lo puede hacer pero no se a donde enviarlo y se cero, me gustaria que vieras mi solucion. sls

  4. Hi Faraz, the challenge is now closed, but stay tuned for the follow-up post to see what kinds of solutions readers sent in!

  5. Got my solution! A bit late due to hollidays...
    You didin't say a word about sorting final results. Is it a part of the puzzle?

  6. Pingback: Learning Power Query (Get & Transform) | My Spreadsheet Lab

  7. Pingback: Power Query Challenge #2 Results - The Ken Puls (Excelguru) BlogThe Ken Puls (Excelguru) Blog

  8. Pingback: Ken Puls Power Query Challenge#2 | My Spreadsheet Lab

  9. I know it is very late, but I attempted this challenge and had some fun! I will head over to the results now.

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.

Latest Posts