Number.RoundTowardZero

D

Back to top

T

Back to top

#

Back to top

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

What is the Number.RoundTowardZero function?

The Number.RoundTowardZero function is used to round a number towards zero to the nearest integer. This function takes a single argument, which is the number that you want to round. If the number is positive, the function will round down to the nearest integer. If the number is negative, the function will round up to the nearest integer. The syntax for the Number.RoundTowardZero function is as follows:

Number.RoundTowardZero(number as nullable number) as nullable number

How does the Number.RoundTowardZero function work?

The M code behind the Number.RoundTowardZero function uses a simple logic to round a number towards zero. If the number is positive, the function subtracts 0.5 from the number and then rounds down to the nearest integer. If the number is negative, the function adds 0.5 to the number and then rounds up to the nearest integer.

Let's look at an example to understand this better. Suppose we have a number 2.8 that we want to round towards zero. The Number.RoundTowardZero function will first subtract 0.5 from 2.8, which gives us 2.3. The function will then round this number down to the nearest integer, which is 2. So, the result of the Number.RoundTowardZero function for the number 2.8 is 2.

Now, let's consider another example where we have a number -2.8 that we want to round towards zero. The Number.RoundTowardZero function will first add 0.5 to -2.8, which gives us -2.3. The function will then round this number up to the nearest integer, which is -2. So, the result of the Number.RoundTowardZero function for the number -2.8 is -2.

How to use the Number.RoundTowardZero function?

To use the Number.RoundTowardZero function in Power Query, you need to follow these steps:

1. Open Power Query Editor by clicking on the "Transform data" button in Power BI Desktop.

2. Select the column that contains the numbers that you want to round towards zero.

3. Click on the "Add Column" tab in the ribbon and select "Custom Column".

4. In the "Custom Column" dialog box, enter a name for the new column and the formula for the Number.RoundTowardZero function. For example, if you want to round the numbers in column "Amount" towards zero, you can use the following formula:

= Number.RoundTowardZero([Amount])

5. Click "OK" to create the new column with the rounded numbers.

The Number.RoundTowardZero function is a simple yet powerful function in the Power Query M language. It is widely used in data transformations and manipulations to round numbers towards zero. In this article, we explored the M code behind the function and how it works. We also looked at how to use the function in Power Query Editor to round numbers towards zero. By understanding the Number.RoundTowardZero function, you can make your data transformations more efficient and accurate.

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)