Forum Discussion

Mic1979's avatar
Mic1979
Post Partisan
1 year ago
Solved

Rounding

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.

Need Help
  • DataNinja777's avatar
    DataNinja777
    1 year ago

    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,

6 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    1 year ago

    In the Add Custom Column dialog:

     

    =Number.Round([Numbers]+.1,0,RoundingMode.AwayFromZero)
  • DataNinja777's avatar
    DataNinja777
    Super User
    1 year ago

    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,

  • Akash_Varuna's avatar
    Akash_Varuna
    Super User
    1 year ago

    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

     

    • PwerQueryKees's avatar
      PwerQueryKees
      Super User
      1 year ago

      It should have been +0.1 and without +1

       

      Number.Round([YourColumn] +0.1 )