How to Count Activities by Person Using Power Query in Excel
Question details
The user needs to count specific activities assigned to individuals when the activity data is spread across multiple columns.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Data analysis and reporting where activity records are stored in a wide format, making standard COUNTIF formulas or PivotTables ineffective.
- Observed behavior
- The user wants to normalize the multi-column activity data to accurately summarize and count the number of activities per person.
Ensure your data is formatted as an Excel Table (by pressing Ctrl+T) before loading it into Power Query. This ensures that any future rows or columns added will dynamically update in your final report.
Use Power Query to Unpivot Columns and Create a PivotTable
Normalize your wide data by unpivoting the activity columns in Power Query, which then allows you to easily summarize the data using a standard PivotTable.
When activities are spread across several columns, traditional functions like COUNTIF become complex and difficult to maintain. Power Query solves this by transforming your 'wide' data into a 'flat' format, which is the ideal structure for PivotTable analysis.
Select any cell inside your data table, navigate to the 'Data' tab on the Excel ribbon, and click 'From Table/Range' to open the Power Query Editor.
In the Power Query Editor, right-click the column header containing the person's name and select 'Unpivot Other Columns' from the context menu. This converts all activity columns into a pair of 'Attribute' and 'Value' columns.
If the 'Attribute' column simply contains generic headers like 'Activity 1' or 'Activity 2' and is no longer needed, right-click it and select 'Remove'. Rename the 'Value' column to 'Activities' by double-clicking its header.
Click 'Close & Load To...' located on the Home tab. In the dialog box, select 'PivotTable Report' and choose whether to place it on a new or existing worksheet.
In the PivotTable Fields pane, drag the person's name field into the 'Rows' area. Drag the 'Activities' field into both the 'Columns' area and the 'Values' area (ensure it says 'Count of Activities') to view the summarized totals.
Analyze Data Seamlessly with WPS Office
If you are looking for a highly capable, cost-effective alternative for handling complex data analysis and PivotTables, WPS Office provides a lightweight and free solution. It offers exceptional performance without the heavy subscription fees.
- 1. Download and Install: Get the free WPS Office suite from the official website and complete the quick installation.
- 2. Open Your Excel File: Launch WPS Spreadsheets and open your existing .xlsx file without worrying about format loss.
- 3. Utilize Powerful PivotTables: Go to the Insert tab and select PivotTable to immediately start summarizing your structured data effortlessly.
Frequently Asked Questions
Why can't I just use COUNTIF for multiple activity columns?
While possible, using COUNTIF across many scattered columns requires complex, array-like formulas that are prone to errors and difficult to maintain. Normalizing the data first ensures counting is accurate, scalable, and completely automated via PivotTables.
What exactly does 'Unpivot Other Columns' do in Power Query?
It takes multiple columns of horizontal data and transforms them into vertical rows. It pairs each distinct value with its corresponding row identifier (like a person's name), turning wide, report-style data into a flat database format.
Will the PivotTable update automatically if I add new activities?
Yes. As long as your original source data is formatted as an Excel Table, you simply need to right-click your PivotTable and select 'Refresh'. Power Query will automatically process the new columns or rows and update your counts.
