I’m an electrician and I build my invoices with Excel, using both the Data Model (Power Pivot) and Power Query. I’m very happy with the result so far and I want to add a functionality: from each invoice I would like to produce a shopping list that I can send to my supplier, so he can send me the right materials for the job.
So far I have a table that looks like this (I simplified it for sharing it here):
| Quantity | Items to buy |
|---|---|
| 10 | so, box |
| 4 | sw1, box, lsoE |
Each line in this table corresponds to a Task (for example, the first line is “installing a single socket”). The items on the right correspond to the keys of items, which I have in another table: everything is linked in the Data Model. So if I install 10 single sockets, I’m going to need 10 sockets and 10 recessed boxes.
And now I want to turn this table into this simpler shopping list, which I can then send to my supplier:
| Quantity | Item |
|---|---|
| 10 | so |
| 14 | box |
| 4 | sw1 |
| 4 | lso |
I will then use the Data Model to replace each Item key by the corresponding item reference and a description; this last part is easy.
To pass from the first table to the later I’m imagining complex bodged ways , but I am convinced that there must exist a simple and clear way to do it with built-in functions.
How shall I do it?
I even question if the first table is a “bad start” and if I should have things structured in a different way. I don’t love that the list of “Items to buy” are represented by a Strings separated by commas. If you think of a better way to do it, let me know.
