#Excel Power Query solves a real life problem – Who eats what?

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.

Power Query Challenge by Computergaga

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:

Power Query Challenge by Computergaga1

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.

Power Query Challenge by Computergaga2

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.

My solution

Here’s my approach to solve the problem

  1. Generate a unique list of dish and identify if it is a appetizer, main or dessert.  Luckily the data is not too bad; they are all grouped together with a constant number. (otherwise, we may need a helper table to do so)
  2. Transform the source table with transpose and un-pivot
  3. Merge the result table with Query 1 to identify the type of dish
  4. Pivot it to get the final table… desserts time. 🙂

You may download a sample file with the queries at the end of the post.

Note: All screenshots used in this post are captured using Excel for Office 365.  If you are using other versions of Excel, you may find the locations and look&feel of buttons a bit different. The concept is the same though.

 

First thing first, Convert the data range into an Excel Table

Excel tip - Power Query Challenge2

I named the Table as “BaseTable” which is our starting point.

Excel tip - Power Query Challenge1

A. Load “BaseTable” to Power query

  • Select any cell of the BaseTable
  1. Go to Data tab
  2. Select From Table/Range

Excel tip - Power Query Challenge3

The following Power Query Editor opened.

Excel tip - Power Query Challenge4

Note: the Query Name is the same as the Table Name.  You may revise it if you need.

The Query Settings pane on the right shows all steps we applied to the query.  Now, let’s remove the step “Change Type” by clicking the X next to it.

Now, we demote the header to get the list of dishes.

  1. Go to Home tab
  2. Click the dropdown menu of “Use First Row as Headers
  3. Use Headers as First Row

Excel tip - Power Query Challenge5

Now we have all the dishes on first row:

Excel tip - Power Query Challenge6

Let’s take a look at the Applied steps on the Query Settings Pane.  The last step “Changed Type” was generated automatically.  We don’t what this step, so let’s remove “Change Type”

Excel tip - Power Query Challenge7

Well, we have set the BaseTable, we can continue to build our query based on it.

B. Create a new Query by referencing the BaseTable, and to prepare a list of dish identifying appetizer, main, dessert

Open the Queries pane on the left,

  1. Right-click “BaseTable”
  2. Select Reference

Excel tip - Power Query Challenge8

Now, we should see the following, which is exactly the result of the last step of “BaseTable“.

  1. Let’s rename it to “Dish” (by right-click on “BaseTable(2) and then rename)
  2. On the Home tab, select “Keep Rows
  3. Keep Top Rows

Excel tip - Power Query Challenge9

In the dialog box,

  1. Input 1 in the “Number of rows”
  2. OK

Excel tip - Power Query Challenge10

By now, we should be seeing on the top row that lists all the dishes.  Let’s transpose it to a column:

  1. Go to “Transform” tab
  2. Transpose

Excel tip - Power Query Challenge11

Here we go:

Excel tip - Power Query Challenge12

We have nine different dishes in total.  The first three are Appetizers, the middle three are Mains, and the final three are Desserts.  Cool.

Let’s Add an Index Column:

Excel tip - Power Query Challenge13

Here we go:

Excel tip - Power Query Challenge13.1

Indeed, I want to label the first three rows as Appetizer, the middle three as Main and the final three as Dessert.  To achieve that, adding a Conditional Column is a simple way (given we don’t have many items).

  1. Go to Add Column tab
  2. Conditional Column

Excel tip - Power Query Challenge14

Input the following from the “Add Conditional Column” dialog box:

Excel tip - Power Query Challenge15

  1. The name of the new column, where I input “Type
  2. Conditions – where Index is less than or equal to 3, they are “Appetizer“; where Index is greater than or equal to 7, they are “Dessert“; everything else is “Main“.  Make sense?
  3. OK

Here we go!

Excel tip - Power Query Challenge15.1

Let’s do some tidy up to conclude this query:

Excel tip - Power Query Challenge15.3

Tip: To select multiple columns and apply data type together, hold Ctrl key

This is the result of the query “Dish“.  We will use it later.

Excel tip - Power Query Challenge15.2

 

C. Create the Output1 query where magical transformation happens

  1. Right-click the “BaseTable” query
  2. Select Reference

Excel tip - Power Query Challenge16

Rename the BaseTable(2) to Output1

Excel tip - Power Query Challenge16.1

Now, we are ready to Transpose the Table

  1. Go to Transform tab
  2. Transpose

Excel tip - Power Query Challenge17

When the Table is transposed, we see that all “Dish” are now under Column 1, where all guests are in the subsequent columns.

Excel tip - Power Query Challenge17.1

This layout is cool for Unpivot!

  1. Right-Click Column 1 header
  2. Unpivot Other Columns (note: this is important because we don’t know how many guests (columns) we will have every time, but we are pretty sure we will have only one columns of “Dish”)

Excel tip - Power Query Challenge18

And now, we have a “Tabular” layout instead.  A dish will repeat itself in Column1 for all guests who picked it.

Excel tip - Power Query Challenge18.1

Let’s do some tidy up before we go to the next transformation.

Excel tip - Power Query Challenge19

The next step is to “LOOKUP” the type of each type.  As an Excel guy, you should know VLOOKUP… but in Power Query, it is “Merge” queries.

Merging the “Output1” query to the “Dish” query

  1. Go to the Home tab
  2. Click the dropdown menu of “Merge Queries”, select “Merge Queries

Excel tip - Power Query Challenge19

In the Merge queries dialog box, select as follow:Excel tip - Power Query Challenge20

In plain English, it tells Excel to merge the Output1 table with Dish table by using the common column “Dish” in both tables. Based on a all rows from the first table (Output1), returns all matched rows from the second table (Dish).

Now, we should have the following:

Excel tip - Power Query Challenge21

Click that “Expand” icon on the column header of “Dish.1” and select the column we want.

Let’s see it in action:

Excel tip - Power Query Challenge21

walala!  There we go!  A tabular table with Dish, Guest, and Type on three columns 🙌🏻

With this layout, Pivot it to our expected output is just a piece of cake. 🙂

Pivot the column “Type”

  1. Select the columnType
  2. Go to Transform tab
  3. Pivot Column

Excel tip - Power Query Challenge22

In the Pivot Column dialog box, select as follow:

  1. Select “Dish” as Values Column (telling Excel to put the “Dish” under pivoted rows/columns)
  2. Expand the Advanced options
  3. Select “Don’t Aggregate(telling Excel not to do any calculation; simply put the values into corresponding rows/columns)
  4. OK

Excel tip - Power Query Challenge23

What a magic!?

We have the Output1 ready!  First challenge solved.

Excel tip - Power Query Challenge24(Output1)

D. Counting the number of each dish

At this point, to count the number of each dish is relatively easy.  We can ride on the steps on Output1 to start.

  1. Right-click Output1
  2. Duplicate

Excel tip - Power Query Challenge25

Let’s rename the duplicated query to Output2

Excel tip - Power Query Challenge26

We can ride on the steps before “Changed Type“.

  1. On the Query Setting pane, right-click the step “Changed Typed”
  2. Delete Until End

Excel tip - Power Query Challenge27

As you see, from there we have a simple table with two columns: Dish and Guest.

Now we can do the counting by using Group By

  1. Go to Transform tab
  2. Group By

Excel tip - Power Query Challenge28

In the Group By dialog box, select as follows:Excel tip - Power Query Challenge29

(Note: New column name is a free input for the new column name.  You may change it if you want to.)

HERE WE GO! 

Excel tip - Power Query Challenge30(Output2)

Challenge two is done!

E. Load the results to worksheet

  1. Go to Home tab
  2. Close and Load
  3. Close & Load To…

Excel tip - Power Query Challenge31

We are not loading it to Table at this point because we have four queries in total.  If we load to Table now, the four queries will be loaded on four separate worksheets as four different tables.  That’s why I select “Only Create Connection” here.

Excel tip - Power Query Challenge32

Now, on the Queries & Connections pan on the left, we should see four queries there.

Excel tip - Power Query Challenge33

To load the Output1 / Output2 to spreadsheet,

  1. Right-click Output1 / Output2
  2. Load To…

Excel tip - Power Query Challenge34

In the Import Data dialog box, select as follows:

  1. Table
  2. Where you want to put it on
  3. OK

Excel tip - Power Query Challenge35

 

MISSION ACCOMPLISHED!

End Result

Let’s test it by adding new guest:

Finish

Simply awesome! 😎

You may download a sample workbook with all the queries HERE.

Hope you like it.

The above involves lots of steps and procedures.  I believe it’s better to demonstrate it with a video, which is under production now available.

In the video, I will also talk about how to handle if a guest pick multiple items for the same types, e.g. what if A guest picked two desserts?  The output should say… “Multiple items selected, please double check”, something like that…

Meanwhile, please leave your comments and let me know what you think about this approach.  Or if you’d like to share your solutions too.

About MF

An Excel nerd who doesn’t work on Excel most of the time at current job...... 🤔 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Power Query and tagged , , , , , . Bookmark the permalink.

7 Responses to #Excel Power Query solves a real life problem – Who eats what?

  1. Alan Murray says:

    Excellent Power Query work MF 😀 Thank you for participating.

    Like

  2. DataScopic says:

    I want to see your video. I see you use the Grasshopper Leg Pluck, and I like your use of the conditional columns. BRAVO!!!

    Like

  3. Wong Ernest says:

    I watched this challenge solved by other in YouTube recently…..

    Best regards,
    ________________________________

    Like

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.