Fix Blank Debit Values in an Excel Power Query PivotTable
Question details
Debit values are blank in an Excel PivotTable while Credit values display correctly, usually because the Debit source field contains mixed data types.

- Product
- Microsoft Excel Power Query, Data Model, and PivotTable
- Device & OS
- not provided
- Scenario
- The user needs the Debit field to aggregate and display correctly in a PivotTable.
- Observed behavior
- The PivotTable shows blank Debit results even though Credit values from the same dataset are displayed.
Open the Power Query Editor and identify the query and Debit column used by the PivotTable. Check whether the PivotTable was created with the Add this data to the Data Model option enabled.
Set the Debit column to a numeric type in Power Query
Use this method when the Debit column contains numbers stored as text, currency symbols, blanks, or mixed values that prevent PivotTable aggregation.
A PivotTable can sum only numeric fields. In Power Query, one text value, error, dash, or formatted currency string can cause the Debit field to be loaded as text instead of a number.
In Excel, select Data > Queries & Connections, right-click the query that supplies the PivotTable, and choose Edit.
In Power Query Editor, select the Debit column and review the column-quality indicators or filter list for text entries, errors, spaces, hyphens, and unexpected values.
Select the Debit column, then choose Transform > Replace Values to replace entries such as "-" or spaces with null or 0 as appropriate. Use Transform > Replace Errors if the column contains errors.
With the Debit column selected, choose Transform > Data Type > Decimal Number or Fixed Decimal Number. If values include currency symbols, remove or clean the symbols before applying the numeric type.
Select Home > Close & Load, then click inside the PivotTable and choose PivotTable Analyze > Refresh to update the Debit totals.

Rebuild the Data Model connection after correcting the source field
Use this method if the PivotTable uses the Data Model and the Debit field remains blank after the Power Query type has been corrected.
Create and refresh PivotTable reports with WPS Spreadsheet
If you need a free, lightweight spreadsheet solution for analyzing cleaned financial data, WPS Office provides a familiar spreadsheet workspace, PivotTable tools, and seamless migration of Excel workbooks. Open your existing files, check numeric Debit fields, and build reports without changing your usual worksheet workflow.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, choose Spreadsheet, and open the existing Excel workbook containing the transaction data.
- 2. Check the Debit source values: Select the Debit column and use Home > Number Format to apply a number or currency format after removing nonnumeric entries.
- 3. Create a PivotTable report: Select the cleaned data range, choose Insert > PivotTable, and place Debit in the Values area to summarize it.

Frequently Asked Questions
Why does my Excel PivotTable show blank Debit values but show Credit values?
The Debit column is often loaded as text because it contains a nonnumeric entry, such as a dash, currency symbol, space, error, or text label. In Power Query, clean the column and set Transform > Data Type to Decimal Number or Fixed Decimal Number.
Should blank Debit entries be replaced with zero or null in Power Query?
Use null when the value is missing and should not count as a transaction amount. Use 0 only when the record genuinely represents a zero Debit amount. You can make the change with Transform > Replace Values.
How do I check whether the Debit field is text in a PivotTable?
Place Debit in the Values area and open Value Field Settings. If Excel offers Count rather than Sum, or Sum produces blanks, inspect the source field in Power Query and verify that its data type is numeric.
Can one text value stop a Data Model field from being summed?
Yes. When data is added to the Excel Data Model, a text value in a numeric-looking column can cause the field to be treated as text. Correct the type in Power Query, use Data > Refresh All, and recreate the PivotTable if necessary.
Why is formatting the Debit column as Currency not enough?
A worksheet number format changes how values look but does not convert text into numbers. Convert the column in Power Query with Transform > Data Type, then refresh the PivotTable.




