Interesting application of Power Query for real life problem – See how to transform table into different layout using Power Query
I came across this Power Query Challenge from Computergaga and I found it really interesting as it seems that it happened to some of my ex-colleagues for event planning before.
I took the challenge. In this post, I will give a possible way of solving it with Power Query. Yes only one of the possible ways.
The Challenge
You’ve got the following table. The headers of columns are different types of dishes that your guests can select from. What underneaths are the guests who pick it. The first there columns are Appetizers, while the middle three and the last three columns are for Main course, and Desserts respectively.
Is the above a bad layout? Well it depends.
If you serve the dinner, it works fine as you know exactly which dish goes to which guest.
However, if you are the event planner and you want to confirm the dishes selected by your guests, this above table is a nightmare to you. Isn’t it? 😂
What you would like to see should be a table like this:
It clearly states who eat what (as appetizer, as main and as dessert). It will certainly save your day! 🙂
And this is exactly the expected result #1 of the challenge.
Expected result #2 is more for the kitchen member, I guess.
They are more concern on the quantity of each dish. They don’t (usually) care who eats what? They just want to know the quantity required. The following summary table will do.
If you want to watch the original video for the challenge and the solution from Computergaga YouTube channel, please click HERE. You can get the sample file to follow along from there too.
