Power Query Problems

How to Count Unique Events and Clients by Month in Excel

Partner Editor Sep 27, 2026 870 views

Question details

The user needs to calculate the number of unique events and distinct clients grouped by month from a raw dataset.

How to Count Unique Events and Clients by Month in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Transforming and normalizing a dataset containing date, client, and activity information to generate a monthly summary report.
Observed behavior: The source data is currently not normalized, making it impossible to directly group by month and calculate distinct counts accurately.

Before you start

Ensure your dataset has clear column headers and no merged cells, and convert your data range into an official Excel Table by pressing Ctrl+T before importing it.

Solution 1Recommended

Use Power Query to Normalize Data and PivotTable to Count

Use Power Query to clean and structure the data by filling down empty dates and unpivoting columns, then summarize the results with a PivotTable.

Power Query is the most efficient way to reshape data that is spread across multiple columns into a flat, normalized format. Once normalized, a PivotTable can easily group the dates and perform distinct counts.

Load Data into Power Query

Select your data table, navigate to the Data tab on the Excel ribbon, and click 'From Table/Range' to launch the Power Query Editor.

Fill Down Dates

Click the header of the Date column. Go to the Transform tab, click the 'Fill' dropdown button, and select 'Down' to replace null values with the correct dates.

Unpivot Activity Columns

Hold the Ctrl key and select both the Date column and the Client Name column. Right-click either of the selected headers and choose 'Unpivot Other Columns'.

Rename and Load Data

Rename the newly generated 'Attribute' and 'Value' columns to match your event data context. Go to the Home tab and click 'Close & Load' to return the normalized data to the worksheet.

Create a PivotTable for Distinct Counts

Select the newly loaded data, go to the Insert tab, and click 'PivotTable'. Check the box for 'Add this data to the Data Model'. Group the Rows by Month, drag the Events field to the Values area, and change the Value Field Settings for the Client field to 'Distinct Count'.

Use Power Query to Normalize Data and PivotTable to Count

Enabling Distinct Count: The 'Distinct Count' option will not appear in the Value Field Settings unless you check the 'Add this data to the Data Model' box when initially creating the PivotTable.

Easily Count Unique Values with WPS Spreadsheet

WPS Spreadsheet offers powerful, built-in PivotTable capabilities that allow you to group dates and calculate distinct counts effortlessly without requiring complex query setups.

1. Open Your Dataset: Launch WPS Spreadsheet, open your workbook, and select the entire data range you want to analyze.
2. Insert a PivotTable: Navigate to the Insert tab on the top ribbon and click the 'PivotTable' button.
3. Enable Data Model: In the Create PivotTable dialog, make sure to check the option to add the data to the Data Model, which unlocks advanced calculation features.
4. Group by Month: Drag your Date field to the Rows area, right-click any date in the table, select 'Group', and choose 'Months'.
5. Apply Distinct Count: Drag the Client field into the Values area, click on it to open Value Field Settings, and select 'Distinct Count' to see unique client numbers per month.

Built-in PivotTable with Distinct Count functionalityFully compatible with Microsoft Excel (.xlsx) formatsIntuitive user interface for fast data summarizationFree and lightweight alternative for daily data analysis

microsoft office alternative - wps office

Frequently Asked Questions

Why is the Fill Down option greyed out in Power Query?

This typically occurs if you have selected a specific cell or row instead of an entire column. Make sure you click directly on the column header (e.g., 'Date') before attempting to use the Fill Down feature.

Can I count unique clients without adding data to the Data Model?

Standard PivotTables do not support the 'Distinct Count' operation. If you do not add the data to the Data Model, you will need to use a complex array formula combining SUM, IF, and FREQUENCY to get unique counts.

How do I group standard dates into months in a PivotTable?

Once you have dragged the Date field into the Rows area of your PivotTable, right-click any date value displayed in the sheet, select 'Group' from the context menu, and highlight 'Months' in the dialog box.

Excel & Data Problems