Changing data type to fixed decimal number incorrectly rounds up number

[alierm]

Hi
I have a column with long decimal values. When I change the data type of that column from decimal number to fixed decimal number, values getting rounded up in a wrong way. For example:
0.25085000000000002 after conversion becomes 0.2509 while
0.25085 after conversion becomes 0.2508.
Could someone explain how does it work?
Thanks in advance

[AlienSx · Accepted solution]

@alierm banker's rounding

[ManuelBolz]

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