Hi,
A friend, restaurant owner, started to organize take away in these Corona times, she organized via Google Forms
Seems like the output of the orders is not that user friendly if she wants to actually see the total quantity of each dish
I was fully confident, I would solve this for her by an unpivot followed by a pivot but it goes wrong with the last step, I get the error message as I mentioned in the title. I tried to add an index column after my first unpivot but the error is still coming
the file can be found attached, thanks for your support!
To have an ideal situation : can I read the data from Google form or is it required to first copy paste into excel?
Kr Claudine
Hi Claudine,
The Index column needs to be added AFTER the unpivot. The idea is that it creates a unique identifier for each row. You can then Pivot the data. You will still get some errors because some of the customers have ordered the same thing in Choice 1 and 2 e.g. Kecia Lewin.
A better way is to add a custom column that brings the quantity into its own column, then fill up and lastly, remove the 'Quantity' rows. See example file attached.
I'm not aware of any way to connect Power Query to Google Sheets, but presumably you could download the Google Sheets file as a .xlsx and then get the data with Power Query
I hope that points you in the right direction.
Mynda
Hi Mynda,
this did ring a bell, thanks a lot, this was the solution !
Kr Claudine
