Rounding

[Mic1979]

Dear all,

I have the folloqing task:

1. When a number is xx.4 or greater, then I need to round it to next integer (xx+1)
2. When a number is xx.3 or smaller, then I need to round it to integer xx

Could you support with this?

Thanks.

[ronrsnfld]

In the Add Custom Column dialog:

=Number.Round([Numbers]+.1,0,RoundingMode.AwayFromZero)

[PwerQueryKees]

Or alternatively

Number.Round([YourColumn] -1, ) +1

[PwerQueryKees]

It should have been +0.1 and without +1

Number.Round([YourColumn] +0.1 )

[Akash_Varuna]

Hi @Mic1979 This Could be achived through custom columns please try thi

• Open the Power Query Editor.

• Add a Custom Column.

• M Formula for the new column:

if Number.RoundDown([YourColumn]) + 0.4 <= [YourColumn] 
then Number.RoundDown([YourColumn]) + 1 
else Number.RoundDown([YourColumn])

If this post helped please do give a kudos and accept this as a solution
Thanks In Advance

[DataNinja777 · Accepted solution]

Hi @Mic1979 ,

In Power Query (M language), you can create a custom column using the Table.AddColumn function. This formula checks the decimal part of the number using Number.Mod([YourColumn], 1). If the decimal part is ≥ 0.4, it rounds up by adding 1 to the integer part; otherwise, it rounds down to the integer using Number.RoundDown([YourColumn]). Here's the code:

= Table.AddColumn(Source, "Custom_Rounding", each 
    if Number.Mod([YourColumn], 1) >= 0.4 
    then Number.RoundDown([YourColumn]) + 1 
    else Number.RoundDown([YourColumn]), type number)

This ensures numbers like 12.4 round to 13, 12.3 stays 12, 5.6 becomes 6, and 5.2 remains 5.

Best regards,

[Mic1979]

Thanks. I will accept this.