Spreadsheets

Power Query: convert amount text to decimal without errors

Convert amount text to decimal in Power Query without losing rows: the right step order, a try-otherwise column and a review table for error rows.

Editorial illustration for Power Query: convert amount text to decimal without errors

In Power Query, clean amount text before you change its type: trim spaces, replace commas and symbols, then set the column to Decimal Number. Done in that order, the type step rarely fails, and any row that still does can be sent to a separate review table instead of vanishing.

Why does the type change throw DataFormat.Error?

Power Query tries to convert text such as "1,250.00 " or "₹450" in one go. A trailing space, a currency sign or a Dr suffix makes it fail, and the cell shows an error saying it could not convert the value to Number. Worse, Power Query often adds an automatic Changed Type step on load, which converts before you have cleaned anything. Open Applied Steps and delete that step, or set Amount to Text first, so cleaning happens on text.

What is the right step order?

  1. Load the sheet with Data, From Table/Range. Make sure the Amount column is Text.
  2. Transform, Format, Trim: removes leading and trailing spaces.
  3. Transform, Replace Values: replace "," with nothing.
  4. Repeat for ₹, Rs. and INR if they are present.
  5. Change Type to Decimal Number, or use Using Locale and pick English (India) if your system settings differ.
  6. Close and Load.

Why in this order? Trimming first stops a space hiding the real prefix. Replacing comma and symbols while the cell is still text is safe because text replacement knows nothing about numbers. The type change goes last because a number cannot hold characters.

StepTransformAmount cell
StartRaw text₹ 1,250.00
TrimRemove outer spaces₹ 1,250.00
Replace ₹Remove the symbol, then trim again1,250.00
Replace commaRemove commas1250.00
Change typeDecimal Number1250

How do you keep the error rows instead of losing them?

Do not use Remove Errors as your safety net, because it deletes the rows, and Replace Errors hides them behind a zero. Both make the total look clean while the data is not. Use a custom column that attempts the conversion and records what failed.

  • Add Column, Custom Column, name it AmountNum, with the formula: try Number.FromText(Text.Replace(Text.Trim([Amount]), ",", "")) otherwise null
  • Rows that convert get a number. Rows that fail get null.
  • Right-click the query and choose Reference to create a second query. In the first, filter AmountNum to non-null values. In the second, filter AmountNum to null and name it Review.
  • Load both. The first feeds your reports, the second is a short list of rows to fix by hand.

Note that Text.Replace is case-sensitive. For Rs. versus RS., apply Text.Upper first or add each spelling you have seen.

Does this help with next month's file?

Yes, that is the real advantage. Power Query stores the steps, so when next month's rows land in the same sheet, one click on Refresh All repeats the whole clean-up. If the bank changes a column heading, the step that names the column will fail with an error saying the column was not found. That is a useful signal: fix the name in the step and refresh again.

Worked example with hypothetical amounts

Say a source table has 200 rows. After the pipeline, 197 rows convert and 3 land in Review, say a row reading "NIL", one reading "1,250.00 Dr" and one with a stray full stop. 197 plus 3 is 200, so nothing has been lost. You fix those three rows at source and refresh, and the Review table empties.

Before you build on the output

  • Delete the automatic Changed Type step that runs before cleaning.
  • Keep Trim, Replace and conversion in a clear order.
  • Route failures to a Review query rather than removing them.
  • Compare row counts and the SUM with the statement total.

Power Query is a good fit if you are comfortable with it; SpendixAI is another route, since it takes PDF, CSV or Excel statements and asks you to review rows before they are saved.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports