Formatting categorical variables for pivot tables and linking to other data in Excel

Question

Excel Example]

Above is the dataset that I am working with. ^

I am creating an interactive dashboard for this dataset by using pivot tables. I have graphs on date, county, and caller type (affiliation). I want to link the call inquiry categories to this data as well. I need the count for each of the call inquiry categories to display in a graph and link with the other data types.

However, with the way that the call inquiries are currently formatted (in individual columns), I cannot group it in pivot tables with the other data categories. I do not want to group together call categories like health effects and testing and have it count as 1.

How can I format the call categories into one column/link it to the other data types?

Answer 1

You need to normalise the table. This can be done with Power Query in just a few steps.

1. Select the source table
2. On the Data ribbon, click "From Table/Range" in the "Get & Transform Data" Group. This will load the data into the Power Query Editor window.
3. Select the first four columns
4. On the Transform Ribbon click Unpivot > Unpivot Other columns. You will now see several rows for each ID and columns for Attribute and Value.
5. Rename the Attribute column and delete the Value column
6. On the Home ribbon, click Close and Load. That loads the table into the workbook, where you can use it as a pivot table source.

screenshots: