Number.RoundAwayFromZero

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Number.RoundAwayFromZero

Syntax

The syntax of the Number.RoundAwayFromZero function is as follows:

Number.RoundAwayFromZero(number as nullable any) as nullable any

The function takes one argument, which is the number to be rounded. The argument must be a numeric value or a value that can be converted to a numeric value. The function returns the rounded number as a nullable any value.

M Code

The M code behind the Number.RoundAwayFromZero function is as follows:

let

Number.RoundAwayFromZero = (number as nullable any) as nullable any =>

if number = null then null else

if number >= 0 then Number.RoundUp(number) else Number.RoundDown(number)

in

Number.RoundAwayFromZero

The code defines a function named Number.RoundAwayFromZero that takes one argument, number. The function first checks if the argument is null and returns null if it is. If the argument is not null, the function checks if the argument is greater than or equal to 0. If it is, the function uses the Number.RoundUp function to round the number up to the nearest integer. If the argument is less than 0, the function uses the Number.RoundDown function to round the number down to the nearest integer.

Examples

Here are some examples of how the Number.RoundAwayFromZero function can be used:

Example 1:

Suppose you have a table of sales data that includes a column named Price. You want to create a new column that shows the price rounded up to the nearest dollar. You can use the following M code to achieve this:

let

Source = sales_data,

#”Added Round Price” = Table.AddColumn(Source, “Round Price”, each Number.RoundAwayFromZero([Price]))

in

#”Added Round Price”

This code adds a new column named Round Price to the sales_data table. The new column is filled with the result of the Number.RoundAwayFromZero function applied to the Price column.

Example 2:

Suppose you have a table of financial data that includes a column named Net Income. You want to create a new column that shows the net income rounded up to the nearest thousand dollars. You can use the following M code to achieve this:

let

Source = financial_data,

#”Added Round Net Income” = Table.AddColumn(Source, “Round Net Income”, each Number.RoundAwayFromZero([Net Income] / 1000) 1000)

in

#”Added Round Net Income”

This code adds a new column named Round Net Income to the financial_data table. The new column is filled with the result of the Number.RoundAwayFromZero function applied to the Net Income column divided by 1000 and multiplied by 1000.

In conclusion, the Number.RoundAwayFromZero function in Power Query M is a very useful tool for rounding numbers up to the nearest integer away from zero. The M code behind the function is simple and easy to understand. By using this function in combination with other Power Query tools, you can perform complex data analysis and manipulation tasks with ease.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)