DataFormat.Error: We couldn't convert to Number means Power Query met a value in your Amount column that is not a plain number. In bank statement data the usual culprits are a Dr or Cr suffix, a rupee sign or comma, a total or opening-balance row, and blank text. Find the rows with Keep Errors, then clean the text before the type-change step.
Which values break the conversion?
| What is in the cell | Why it fails | How to fix it |
|---|---|---|
| 1,250.00 Dr | The letters Dr are not part of a number | Remove or interpret the suffix |
| ₹1,250 | The currency sign | Remove the symbol, then convert |
| Total | A text row inside the data | Remove that row |
| (empty text) | Empty text is not zero or null | Replace with null |
| 1.250,00 | Locale uses the comma as a decimal point | Change type using locale |
| - | A dash used as a placeholder | Replace with null |
The data may look like a number in the preview, since the preview is not strict. The error appears once the Changed Type step runs over every row.
How do I find the failing rows?
- Click the step that shows the error in Applied Steps. Some cells in the column show Error; click beside one to read the message.
- Choose Home, Keep Rows, Keep Errors. Now only the failing rows are left, so you can read them.
- Look at what the value is in the original column. Go back to the step before the type change to see the raw text.
- Note the pattern: always a suffix, always on one row, or always a symbol.
Do not use Remove Errors as a first reaction. It deletes the failing rows quietly, and your totals will be short by exactly those amounts, with no warning.
What should I do for each culprit?
Fix the text in a step that comes before the type change. For Dr and Cr suffixes, this custom column keeps digits, a point and a minus sign, then converts the result:
- Table.AddColumn(Source, "AmountNum", each try Number.FromText(Text.Select([Amount], {"0".."9", ".", "-"})) otherwise null, type number)
Text.Select keeps only the characters listed, so commas, ₹, spaces and the letters Dr or Cr all disappear. The try ... otherwise null part turns anything that still fails, such as empty text, into an empty value rather than an error. That answers the amount, but not the sign. If your statement shows debits with Dr, add a second step: if Text.EndsWith(Text.Trim([Amount]), "Dr") then -[AmountNum] else [AmountNum]. Read your own statement to see which suffix means money out, and do not assume.
- Total and opening-balance rows: filter them out with the column filter, or use Table.SelectRows(Source, each [Date] <> null) to keep only rows that have a date.
- Trailing spaces: use Transform, Format, Trim before the conversion.
- A comma decimal: right-click the column, choose Change Type, Using Locale, pick Decimal Number and a locale that matches the file.
Why does it fail for one month but not another?
Banks and card issuers change export layouts, and each statement can carry its own surprises: a footer line with a total, an extra header repeated on page two of a PDF-to-Excel export, or a closing balance that ends in Cr. A query that worked in April can fail in May for this reason alone. Treat each new month as a small test, and look at the last rows of the file as well as the first.
How do I stop it coming back?
- Do cleaning steps before the type step, so a new statement with the same quirks passes.
- Keep try ... otherwise null in the conversion, and add a final step that counts nulls in AmountNum. A count above 0 is a warning that a new format has appeared.
- Keep one sample file of each bank's layout beside the workbook for testing.
Statement layouts also change without notice, which is why SpendixAI shows the parsed rows for review before you save an import, instead of trusting every layout to read perfectly.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

