Pivot Table Issues

Fix Blank Debit Values in an Excel Power Query PivotTable

Khadija KhanKhadija Khan Nov 12, 2026 869 views

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.

Fix Blank Debit Values in an Excel Power Query PivotTable
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the query editor

In Excel, select Data > Queries & Connections, right-click the query that supplies the PivotTable, and choose Edit.

2
Inspect the Debit column

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.

3
Replace nonnumeric 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.

4
Apply a numeric data type

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.

5
Reload and refresh the report

Select Home > Close & Load, then click inside the PivotTable and choose PivotTable Analyze > Refresh to update the Debit totals.

Set the Debit column to a numeric type in Power Query
Debit values can be summed after conversion: When every loaded Debit value is numeric or null, the PivotTable should show the field as a numeric value that can be summarized by Sum.
Free Microsoft Office alternative

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. 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, choose Spreadsheet, and open the existing Excel workbook containing the transaction data.
  2. 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. 3. Create a PivotTable report: Select the cleaned data range, choose Insert > PivotTable, and place Debit in the Values area to summarize it.
Create PivotTables to summarize Debit and Credit amounts after source data is cleaned.Open and edit Microsoft Excel .xlsx, .xls, and .csv files with strong format compatibility.Use a familiar ribbon-style interface for quick migration from Microsoft Excel.Stay productive with a lightweight, free office suite for spreadsheet reporting.
microsoft office alternative - wps office

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.