Slicers, pivot tables, and multiple items per column
I'm trying to build a reporting dashboard to take information and show different metrics about that information. It's an obvious job for slicers and pivot tables but I'm having trouble figuring out how to handle issues from multiple items appearing in each column. Data is coming in from MS forms, which does affect the input options.
This is a good representation of my issue. Obviously categories and names are made up.
| saleman | type | equpiment | hours |
|---|---|---|---|
| Alice | cookies;cakes | mixer | 3 |
| Bob;Charlie | candy;cookies | oven;stove | 2 |
| Alice;Charlie | cakes;candy | mixer;oven | 4 |
There are reasons to want to track the number of hours a salesman is associated with. But also the number of hours associated with equipment, etc.
I'm fairly comfortable with using queries to split on delimiter, unpivot those generated columns, etc. then pivoting back around to get hours associated with each salesman, or each type, or equipment. I'd like to have a pivot table for each category, and also slicers to cut on each.
All of the examples I can find online work when there's one item per column, or at worst multiple in a single column. Is there a best guide somewhere on how to handle this or at least better terms to search for? Thanks.