Format number (formatnumber)

[ 1 Description ] [ 2 How does it work in the search window? ] [ 3 How does it work in LINQ? ]

Description

Converts a number according to a format and an optional locale. It is used to separate the digits following the specified format. The result will be a new column containing the strings formatted as specified, depending on the locale (if set).

How does it work in the search window?

Select Create column in the search window toolbar, then select the Format number operation. The conversion is the result of formatting the number specified in the Number argument, using the format specified in the Format, like #,###.0 argument, and applying the rules of the locale indicated in the third optional argument Locale (es, en-gb...).

Argument	Data type

Argument	Data type
Number ^mandatory	integer, float
Format, like #,###.0 ^mandatory	string
Locale (es, en-gb...) ^optional	string

The Format, like #,###.0 argument accepts the following patterns:

0	digit, zero padding (if the number is too short, zeros -in the locale's numeric set- will be added)
#	digit, no zero padding (if the number is too short, there is no need of adding zeros)
.	decimal separator or monetary decimal separator
-	negative sign
,	grouping separator
%	multiply by 100 and show as a percentage

Some Locale (es, en-gb...) examples are:

es, es_ES
en, en_US, en_GB, en_AU
fr, fr_FR

The data type of the values in the new column is string.

Example 1

In the demo.ecommerce.data table, we want to transform the format of the values in the bytesTransferred column to #,###. Besides, we want to consider the Spanish (Spain) locale (in Spanish, a period . is used as standard thousand separator). To do it, we will create a new column using the Format number operation.

The arguments needed to create the new column are:

Number - bytesTransferred column
Format, like #,###.0 - Click the pencil icon and enter #,###
Locale - Click the pencil icon and enter es_ES

Click Create column and you will see the following result:

How does it work in LINQ?

Use the operator select... as... and add the operation syntax to create the new column. These are the valid formats for the Format number operation:

formatnumber(number, string_format)
formatnumber(number, string_format, string_locale)

Example

You can copy the following LINQ script and try the above example on the demo.ecommerce.data table.

from demo.ecommerce.data
  select formatnumber(bytesTransferred, "#,###", "es_ES") as bytesTransferred_formatted