Changing data type to fixed decimal number incorrectly rounds up number

Hello alierm,

this is a common problem in almost all IT systems or software solutions. Rounding large numbers often leads to problems.

In my solution I wrote you a function. You can adapt this function to your needs. This allows you to define your own “rounding logic”.

Please get in touch if you have any questions.

Custom function:
1) In Power Query
2) Create a new Query

3) Paste this Code

4) Rename the Query to "fnCustomRound"

(RoundNumber as number, DecimalPlaces as number) as number =>
let
    Multiplier = Number.Power(10, DecimalPlaces),
    ScaledNumber = RoundNumber * Multiplier,
    RoundedNumber = if Number.Abs(ScaledNumber - Number.RoundDown(ScaledNumber)) >= 0.5
                    then Number.RoundUp(ScaledNumber)
                    else Number.RoundDown(ScaledNumber),
    Result = RoundedNumber / Multiplier
in
    Result

In your Data Query:
1) Add the following Code

 ColumnCustomRounded = Table.AddColumn(#"NAME OF THE PREVIOUS STEP", "RoundedColumn", each fnCustomRound([#"NAME OF YOUR DECIMAL COLUMN"], 4))

Best regards from Germany
Manuel Bolz

If this post helped you, please consider Accept as Solution so other members can find it faster.

🤝Follow me on LinkedIn