Power Query Problems

Use Excel Power Query to Put Election Dates in One Row per Registrant

Steve KSteve K Nov 18, 2026 869 views

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.

Put Election Dates in One Row per Registrant with Excel Power Query
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.
Before you start

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.

Solution 1Recommended

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.

1
Load the source table

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.

2
Group by registrant

Select RegistrantID, choose Home > Group By, and group by RegistrantID. Name the new column Rows and select All Rows as the operation.

3
Number dates within each group

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.

4
Expand and pivot the dates

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.

5
Load and refresh the result

Choose Home > Close & Load. After adding records to the source table, use Data > Refresh All to update the result.

Group and pivot election dates with Power Query
Check output width: Each registrant's dates are numbered in ascending order, including repeated dates. An Excel worksheet has a 16,384-column limit, so a registrant with a very large number of records may require a different output layout.
Summarize election records in WPS

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. 1. Open the records: Open the Excel workbook in WPS Spreadsheet and select the range containing RegistrantID and ElectionDate.
  2. 2. Insert a PivotTable: Choose Insert > PivotTable and select a location for the report.
  3. 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. 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.
Summarize election records by registrant and dateSee repeated records as counts in the date columnsOpen and edit Microsoft Excel XLSX filesRefresh the PivotTable when the source data changes
microsoft office alternative - wps office

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.