How to Find and Count Common Sequence Numbers Using Power Query
Question details
The user needs to extract comma-separated sequence numbers associated with specific IDs and count the occurrences of each unique sequence number.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Analyzing tabular data where sequence numbers are stored as comma-separated strings within a single cell, requiring separation and frequency counting.
- Observed behavior
- The data needs to be normalized so that each sequence number is separated into its own row and counted accurately across different IDs.
Ensure your source data is formatted as a structured table (Ctrl+T) before importing it into Power Query to prevent range reference errors during data transformation.
Use Power Query and Pivot Tables to Split and Count Data
This method uses Power Query to split the comma-separated strings, unpivot the data into a single column, and then relies on a Pivot Table to count the frequency of each sequence number.
Power Query is highly effective at normalizing nested or comma-delimited data. By splitting strings into multiple columns and then unpivoting them, we prepare the dataset for standard pivot table aggregation.
Select your data table containing the SubcellId and SN (Sequence Number) columns, go to the Data tab on the ribbon, and choose 'From Table/Range' to open the Power Query Editor.
Select the SN column. Navigate to the Home tab, click 'Split Column' > 'By Delimiter', choose 'Comma' as the delimiter, and select 'Split at each occurrence of the delimiter' to separate the values into multiple columns.
Select the SubcellId column, right-click its header, and choose 'Unpivot Other Columns'. This transforms all the split sequence number columns into two distinct columns: 'Attribute' and 'Value'.
Remove the unnecessary 'Attribute' column. Filter out any empty or null values in the 'Value' column, and then click 'Close & Load' from the Home tab to output this cleaned dataset into a new worksheet.
Highlight the newly loaded data table, go to the Insert tab, and click 'PivotTable'. Drag the normalized SN 'Value' field into the Rows area, and drag it again into the Values area (ensuring it is set to 'Count') to see the occurrences of each sequence number.
Analyze Sequence Numbers Effortlessly in WPS Spreadsheet
While Power Query is an excellent tool for complex workflows, you can easily achieve the exact same normalization and counting results using the built-in Text to Columns and PivotTable features in WPS Spreadsheet.
- 1. Split Comma-Separated Values: Select the column containing the comma-separated sequence numbers, go to the Data tab, click 'Text to Columns', and choose comma as your delimiter.
- 2. Rearrange the Data: Copy the newly split columns and paste them below each other in a new sheet (using Paste Special > Transpose if necessary) to form a single continuous column of sequence numbers.
- 3. Remove Blanks and Clean Data: Select the new column, use the 'Find and Replace' or 'Go To Special' feature to locate and delete blank cells to ensure a clean, continuous dataset.
- 4. Insert a PivotTable: Select your reorganized dataset, go to the Insert tab, and choose 'PivotTable'. Drag the sequence numbers to the Rows area and Values area (as Count) to instantly see the frequency of each number.
Frequently Asked Questions
Why are identical sequence numbers not grouping correctly in the Pivot Table?
This usually happens due to hidden trailing or leading spaces that remain after splitting by commas. Use the TRIM function in a helper column or Power Query's Format > Trim feature to clean the text before creating your Pivot Table.
Can I count sequence numbers without unpivoting the columns?
While it is technically possible using complex array formulas like COUNTIF across multiple columns, unpivoting the data into a single continuous column is highly recommended. It creates a normalized dataset that pivot tables can process efficiently and accurately.
What happens if my sequence numbers exceed 4095?
Power Query and Pivot Tables can handle sequence numbers of virtually any size or range. As long as your data type is correctly set to whole numbers or text during the query steps, the count aggregation will function perfectly regardless of the numerical value.
How do I refresh the count if new sequence numbers are added to the source data?
If you used Power Query, simply right-click anywhere inside the outputted data table and select 'Refresh'. The query will automatically execute all steps (split, unpivot, clean), after which you can click 'Refresh' on your Pivot Table to see the updated counts.
