Mynda,
I have a sales table with row index such as 1, 2, 3, 3, 4, 5, 5, 6, 7, 8,, 9,10, 11, 11, 12, 12, 13, 14, 15..... (please see the attachment)
Each number denotes a child. The duplicates mean families with two kids while non-duplicates mean families with one kid.
How can I group them with Power Query? Or How can I use Pivot Table to group sales for families with one child and families with two kids? What is the best way to do it?
Thanks!
Jim
Hi Jim,
You can do this easily with a PivotTable. Put the 'Kids' field in both the Rows and Values area of the PivotTable. Set the field in the Values area to Count instead of Sum.
If you want to filter just those with 2 or more kids then you can also do so in the PivotTable. Similarly you could just sort the data, however you weren't clear on how you wanted the end result to look.
Mynda
Mynda,
Thanks for your help. It works. How can I further group them into two groups (1 kid families & two kids families) with their respective revenues and their respective number of kids? Please see the attachment for clarification.
Jim
Hi Jim,
You need to add a helper column to the source data that classifies the kids into 1 or 2 kid families. See attached.
Mynda
