Power Query Challenge - What Did They Order from the Menu?

In this video, we have a Power Query challenge to solve. Hey, you can solve it in a different way if you like, but I used Power Query.

We have a list of options that guests have chosen from a menu for a dinner party. We are the caterers and we want a list of who ordered what.

Unfortunately the data is organised in a way that makes it very difficult to read.

With Power Query we will make two lists. One with guests' names and what they ordered. And another with the options and how many of each option was ordered.

Find more great free tutorials at;

*** Online Excel Courses ***

Connect with us!

[pmsocho]

Cool challenge!
My approach, 9 steps, no helper table:


let
Source = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
#"Demoted Headers" = Table.DemoteHeaders(Source),
#"Transposed Table" = Table.Transpose(#"Demoted Headers"),
#"Added Index" = Table", "Indeks", 0, 1),
#"Integer-Divided Column" = Index", {{"Indeks", each Number.IntegerDivide(_, 3), Int64.Type}}),
#"Unpivoted Other Columns" = Column", {"Indeks", "Column1"}, "Atrybut", "Wartość"),
#"Removed Columns" = Other Columns", {"Atrybut"}),
#"Pivoted Column" = Columns", {{"Indeks", type text}}, "pl-PL"), Columns", {{"Indeks", type text}}, "pl-PL")[Indeks]), "Indeks", "Column1"),
#"Renamed Columns" = Table.RenameColumns(#"Pivoted Column", {{"0", "Starter"}, {"1", "Main"}, {"2", "Dessert"}})
in
#"Renamed Columns"

[williamstan]

Hi Alan

Tks for the video, very informative

I actually have a problem with power query which I wonder if I can seek for your advice

I wanna set auto refresh on both pivot table and power query

The data used in the pivot table is the output of power query.

After i set an VBA workbook.refreshall on the source data sheet, and disable the background refresh, everytime I open the worksheet, it just continously non stop refreshing the workbook which I forced me to end the application.

I wonder if I did anything wrong?

[TruthofDilly]

How do you transform data? I’ve tried to right click but the numbers still look incorrect

[MySpreadsheetLab]

Thanks for the video Alan! Power Query is amazing....and now I'm craving French Onion Soup!!

[mattmatt]

What was the point of inserting index column before modulo ?

[wayneedmondson]

Hi Alan.. brilliant! Thanks for posting a link to the file and your in-depth article which is excellent! This is a great example and challenge for practicing with Power Query. Thanks for sharing it and your useful tips, videos, courses and web site all of which I find to be both invaluable and entertaining. Thumbs up!

[mohammedimani]

Another great video. You are amazing!!

[EricaDyson]

Nice one and excellent session. Thanks so much.