How to Count Unique Entries Across a Dynamic Excel Range with Power Query in WPS Office
Question details
The user needs to count unique entries across a changing Excel range imported from SharePoint via Power Automate, using Power Query to create a separate result table without manually listing every possible entry.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- Data is imported from SharePoint through Power Automate and contains entries spread across multiple rows and columns; the user wants a refreshable count by entry.
- Observed behavior
- Source data changes dynamically and must be aggregated into a separate result table that updates when the query is refreshed.
Open the workbook that contains the imported SharePoint tables or the source query in WPS Office Excel. Confirm the source data is arranged as a structured range or Excel Table so Power Query can include new rows automatically.
Count unique entries by appending, unpivoting, and grouping in Power Query
Use this method when source tables are imported from SharePoint or Power Automate and you need a refreshable count of values across several columns.
This approach appends all incoming tables into one query, converts multiple value columns into a single list, and then groups repeated values. The grouped query can be loaded to a separate result table and refreshed when source data changes.
In WPS Office Excel, select the imported source table or query, then go to the Data tab and click Get Data > Launch Power Query Editor.
In Power Query Editor, use Home > Append Queries to combine the source tables that contain the entries you want to count.
Select the columns that contain the entries you want to count, then choose Transform > Unpivot Columns to turn those multiple columns into a single Value column.
Select the unpivoted value column and apply Home > Remove Rows > Remove Blank Rows to exclude empty entries.
Select the Value column, go to Home > Group By, set Group By to the value column and operation to Count Rows, then rename the new column to Count.
Click Home > Close & Load To, choose Table and a new or existing worksheet, then click OK to output the grouped count table.
Count Unique Entries in Dynamic Excel Data with WPS Office Power Query
WPS Office Excel includes Power Query tools for appending, unpivoting, and grouping dynamic data from SharePoint or Excel Tables, so you can create a separate count table and refresh it when new entries arrive.
- 1. Open your workbook: Open the Excel workbook that contains the imported SharePoint or Excel Table data in WPS Office Excel.
- 2. Launch Power Query: Click the Data tab and select Get Data > Launch Power Query Editor.
- 3. Append and unpivot: Append the source tables, select the entry columns, and use Transform > Unpivot Columns.
- 4. Group and count: Remove blank rows, then use Home > Group By to count each unique value.
- 5. Load and refresh: Close and load the grouped query to a result table; refresh the query whenever the source data changes.
Frequently Asked Questions
Can Power Query count unique values from multiple columns at once?
Yes. Append the source tables, select the columns that contain entries, unpivot them into one Value column, remove blank rows, and use Home > Group By with Count Rows.
How do I make the result table update when SharePoint data changes?
Load the grouped query to a table and refresh it manually, or configure Power Automate to run the refresh after each import. If the source is an Excel Table, new rows are included automatically when the query refreshes.
Do I have to list every possible entry manually?
No. Power Query grouping counts each unique value automatically after you unpivot the relevant entry columns and remove blank values.
Where can I find Unpivot Columns in WPS Office Excel?
In the Power Query Editor, select the columns you want to unpivot, then go to Transform > Unpivot Columns.
What should I do before grouping if there are empty cells?
Select the unpivoted Value column and choose Home > Remove Rows > Remove Blank Rows to prevent empty entries from being counted.
