How to Fix Power Query Values Showing as Count Instead of Sum in Excel
Question details
The user needs to correct a PivotTable summarizing Power Query data where a calculated column displays as a Count instead of a Sum.
- Product
- Microsoft Excel (Power Query)
- Device & OS
- not provided
- Scenario
- Analyzing and summarizing Power Query data outputs using an Excel PivotTable.
- Observed behavior
- Calculated Power Query columns default to Count instead of Sum when dragged into the PivotTable Values area, and return zero if manually switched to Sum.
Ensure you have saved your workbook and identify the exact name of the calculated column in your PivotTable that is failing to sum correctly.
Change the Column Data Type to Numeric in Power Query
The primary reason a PivotTable defaults to Count instead of Sum is that the source data is formatted as text or contains errors. Changing the data type directly in Power Query resolves this underlying issue.
PivotTables automatically default to 'Count' if they detect any non-numeric values, such as text strings, blanks, or errors in the source column. Changing the cell format in the Excel sheet is not enough; the conversion must be done inside the Power Query Editor so the data is loaded as actual numbers.
Navigate to the 'Data' tab on the Excel ribbon, click on 'Queries & Connections', and double-click the relevant query to launch the Power Query Editor.
Scroll through your query to find the specific calculated column (e.g., [PayInvoiceAmt]+[Deposit]) that is showing up as 'Count' in your PivotTable.
Click the small data type icon on the left side of the column header (it likely shows 'ABC' or 'Any'). Select 'Decimal Number', 'Whole Number', or 'Currency' depending on your calculation.
Click 'Close & Load' on the Home tab of the Power Query Editor. This applies the numeric data type changes and updates your Excel worksheet table.
Right-click anywhere inside your PivotTable and select 'Refresh'. Remove the problematic field from the Values area and drag it back in; it should now default to 'Sum'.
Try WPS Office for Seamless Data Analysis
Struggling with complex Power Query data typing errors in Microsoft Excel? WPS Spreadsheet offers a robust, highly compatible, and user-friendly alternative for all your data processing and PivotTable needs without the steep learning curve.
Frequently Asked Questions
Why does my Excel PivotTable always default to Count?
A PivotTable defaults to Count when it detects that the source data is not strictly numerical. If even a single cell in the column is formatted as text, contains a blank, or holds an error, Excel assumes the entire column is non-numeric and uses the Count function instead.
Can I just change the cell format to 'Number' in Excel to fix this?
No, simply changing the cell formatting on the Excel ribbon to 'Number' changes how the data looks, but it does not change the underlying data type imported by Power Query. You must change the data type within the Power Query Editor itself.
How do I force a PivotTable to Sum instead of Count manually?
You can right-click the value field in the PivotTable, select 'Value Field Settings', and choose 'Sum' from the list. However, if the underlying source data is still being treated as text by Power Query, forcing a Sum will result in a value of zero.
Does removing blanks fix the Count vs Sum PivotTable issue?
Yes, if blanks are the reason Excel is reading the column as non-numeric. You can replace blank or null values with a '0' in Power Query before changing the column type to a Decimal or Whole Number to ensure the PivotTable defaults to Sum.
