VALUE
Converts a text value that looks like a number into a number.
VALUE(<text>)Returns: A number (a scalar). An error when the text cannot be read as a number.
What it does
VALUE turns text that holds a number into a real number, so it can be added, compared and summed. Imported files often hold amounts as text, which looks right on screen but adds up to nothing. VALUE is the bridge. It works with text that contains only a number, and it depends on the locale for the decimal and thousands symbols.
Syntax and arguments
- text: a text value that holds a number, such as "1234.50".
Example
A bank file stores each amount as text. The reconciliation needs the amounts as numbers to total them against the ledger.
Bank Amount = VALUE(BankLines[AmountText])
A text value of "1234.50" becomes the number 1234.5. Once converted, the column can be summed, and the total can be compared with the ledger total.
Example: a second, different use
Remove the thousands separator first when the text carries one, then convert:
Invoice Total = VALUE(SUBSTITUTE(Invoices[TotalText], ",", ""))
A value of "12,450.00" becomes 12450, which the model can add. This combination is common in bank and marketplace imports.
Things to watch
- A text that cannot be read as a number is an error. Use IFERROR when a few rows may hold notes such as "pending".
- VALUE depends on the locale. A comma may be a decimal point in some regions, so test a sample from the source.
- Currency symbols and spaces can break the conversion. Remove them with SUBSTITUTE or TRIM first.
- Once a column is a number in Power Query or the data source, VALUE is not needed. Prefer fixing the type at the source.
- Test for blanks before converting, so a missing value does not show up as an unexplained zero.
In business terms
VALUE is the step that makes imported bank, payroll and marketplace data usable. Without it, a total that should match the ledger shows zero, and the reconciliation looks broken when only the data type is wrong.