Use Excel Power Query to Put Election Dates in One Row per Registrant
Question details
The user needs to restructure election records so each registrant appears once with their election dates across columns or in a combined record.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- A large source contains multiple RegistrantID and ElectionDate rows for each registrant.
- Observed behavior
- The desired result is one row per registrant containing that registrant's election dates.
Check that the source has consistently named RegistrantID and ElectionDate columns and that election dates are recognized as dates. Convert worksheet data to an Excel table so new rows are included when the query is refreshed.
Group and pivot election dates with Power Query
Use this method to create a refreshable table with one registrant per row and each election date in a separate numbered column.
Select a cell in the source table, then choose Data > From Table/Range to open Power Query Editor. Set ElectionDate to the Date data type if needed.
Select RegistrantID, choose Home > Group By, and group by RegistrantID. Name the new column Rows and select All Rows as the operation.
Choose Add Column > Custom Column and enter Table.AddIndexColumn(Table.Sort([Rows], {{"ElectionDate", Order.Ascending}}), "DateNumber", 1, 1, Int64.Type). Name the new column IndexedRows.
Remove the original Rows column, then use the expand button on IndexedRows to select ElectionDate and DateNumber. Change DateNumber to Text, select it, and choose Transform > Pivot Column. Set Values Column to ElectionDate and select Don't Aggregate under Advanced options.
Choose Home > Close & Load. After adding records to the source table, use Data > Refresh All to update the result.

Summarize registrants and election dates in a PivotTable
Use a PivotTable when date column headers and record counts are sufficient; it does not place each date into a numbered value column.
Create a registrant-by-date summary in WPS Spreadsheet
Use a PivotTable in WPS Spreadsheet to show one row per registrant, with election dates as column headers and counts indicating their records.
- 1. Open the records: Open the Excel workbook in WPS Spreadsheet and select the range containing RegistrantID and ElectionDate.
- 2. Insert a PivotTable: Choose Insert > PivotTable and select a location for the report.
- 3. Set the report fields: Drag RegistrantID to Rows, ElectionDate to Columns, and RegistrantID to Values. Set the Values calculation to Count if needed.
- 4. Review the summary: Read each date in the column header and its record count in the registrant's row. Refresh the PivotTable after updating the source data.

Frequently Asked Questions
How do I update the Power Query result with new records?
Add the records to the source Excel table, then choose Data > Refresh All. The query repeats the grouping and pivot steps.
Will duplicate election dates be retained?
Yes. The Power Query method assigns an index to every source row within each registrant, so repeated dates appear in separate numbered columns.
Why do I need an index before pivoting?
The per-registrant index gives each date row a distinct column position. Without it, multiple dates for the same registrant cannot be placed into separate numbered columns using this pivot.
Can a PivotTable show the dates themselves in the cells?
The PivotTable method shown uses ElectionDate as column headers and displays counts in the cells. Use the Power Query method if you need the dates as values in numbered columns.




