Formats a numeric set of values according to the specified number formatting. Source values can be a reference to a column containing Integer or Decimal values.

supports a wide variety of number formats, following Java standards. For more information, please see Java's documentation.

NOTE: This function just changes how the underlying cell value is displayed. If you round the value to a specific level of precision, please use the ROUND function. See ROUND Function.

derive type:single value: NUMFORMAT(MyPrice, '$###,###.##') as: 'dateformat_MyPrice'

Output: Generates a column of values from the MyNum column converted to a price format.

derive type:single value:NUMFORMAT(numeric_col, number_format_string)

ArgumentRequired?Data TypeDescription
numeric_colYstring, integer, or decimalName of Integer or Decimal column whose values are to be formatted
number_format_stringYstringLiteral value of the number formatting string to apply


Name of the column whose Integer or Decimal data is to be formatted.


Required?Data TypeExample Value
YesString (column reference) or Integer or Decimal literalMyPrice


String value indicating the number format to apply to the input values.

NOTE: You cannot create number format strings in which a 0 value appears before a # value. The following example strings are not supported: #.#0, #.#0#, #.#00


Some key codes:

CodeDescriptionExample Format StringExample InputsExample Outputs
#Insert a digit if it is present in the data.










0Insert a digit even if it is not present in a data.'00.##'





$You can add constants values to the expression. Whitespace is respected.
For example, you can insert currency markers at the beginning of your expression.
'$ ##.##'20
$ 20
$ 2514.22
$ 6.67
%Percentage expressions can be at the back of the number formatting expression.'##.## %'20
20 %
2514.22 %

supports Java number formatting strings, with some exceptions.

Missing values for this function in the source data result in missing values in the output.

Required?Data TypeExample Value

Example - formatting price and percentages

This example steps through how to manage number formatting for price and percentage data when you have to perform some computations on the data in the application.


In this case, you need to compute sub-total and totals columns.


When this data is first imported into the Transformer page, you might notice the following:

NOTE: Where possible, remove currency and three-digit separators from your numeric data prior to import.

You can re-type the OrderId column to String without issue. If you retype the other three columns, all values are mismatched. You can use the following transforms to remove the currency and percentage notation. The first transform removes the trailing % sign from every value across all columns using a .

replace col: * on: `\%{end}` with: ''

You can use a similar one to remove the $ sign at the beginning of values:

replace col: * on: `{start}\$` with:''

When both are applied, you can see that the data types of each column is updated to a numeric type: Integer or Decimal. Now, you can perform the following computations:

derive type:single value:(Qty * UnitPrice) as:'SubTotal'

You can use the new SubTotal column as the basis for computing the DiscountedTotal column, which factors in discounts:

derive type:single value:(SubTotal - (SubTotal * (Discount / 100))) as:'DiscountedTotal'

The Total column applies the tax to the DiscountedTotal column:

derive type:single value: DiscountedTotal * (1 + (TaxRate / 100)) as: 'Total'

Because of the math operations that have been applied to the original data, your values might no longer look like dollar information. You can now apply price formatting to your columns. The following changes the number format for the SubTotal column:

set col:SubTotal value:NUMFORMAT(SubTotal, '#.00')

Note that the leading $ was not added back to the data, which changes the data type to String. You can apply this transform to the Price, DiscountedTotal, and Total columns.

NOTE: The data types for your columns should match the expected inputs for your downstream analytics system.

The Discount and TaxRate values should be converted to decimals. The following adjusts the Discount column:

set col:Discount value:(Discount / 100)


The output data should look like the following:


See Also