Forum Discussion
Excel pivot table help @members
pan-g I replicated a small part of your table and used Power Query to extract and count the names per Topic.
See if this does what you had in mind. File attached.
pan-g I replicated a small part of your table and used Power Query to extract and count the names per Topic.
See if this does what you had in mind. File attached.
- How do you do it, could you guide me please.
pan-g If you are new to Power Query, you could start learning by reading through the web-site in the link below. All was done by clicking in the user interface. No complicated manual programming steps were involved. Though, you have to learn which buttons to press and when.
https://exceloffthegrid.com/power-query-introduction/
Once you have connected to the data table, you need to flatten it. In PQ language that's called unpivotting. That transforms a table with separate columns for each department
to a table with only three columns. One for the topic, one for the department and one for the group of names.
Now, understand that the multiple names in one cell are separated by a line-feed in Excel to make them wrap around in a single cell. In PQ you can split the names column into rows based on these line-feeds to turn the previous table into this:
The final step is to Group by topic, counting the rows in each group.
PQ is not difficult to learn, but it takes a while to get into it and you may feel a bit lost in the beginning. Don't let it scare you, though. It's going to be worth the effort.
