Rounding problem in both Power Query Editor and Dataflow on web

[Kentz]

When using rounding to 2 decimals, the result is,

0.005 = 0

0.015 = 0.02

0.025 = 0.02

0.035 = 0.04

0.045 = 0.04

0.055 = 0.06

0.065 = 0.06

0.075 = 0.08

0.085 = 0.08

is this a bug?

[KNP · Accepted solution]

Hi @Kentz, 

There was a blog post somewhere explaining this, I can't find it right now but will link it if I do.

I think adding the optional rounding mode will give you what you're looking for.

See last step below...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0jMwMFWK1QExDGEMIxjDGMYwgTFMYQwDExjDUCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type number}}),
    #"Inserted Rounding" = Table.AddColumn(#"Changed Type", "Round", each Number.Round([Column1], 2, RoundingMode.Up), type number)
in
    #"Inserted Rounding"

Hope this helps.

Kim

[Kentz]

CHAMP!! That worked. Thanks so much!

[v-yetao1-msft]

Hi @Kentz 

Is the column you set up as a regular numeric column or is it generated by dax ? And what’s your datasource ?

I enter data in desktop directly and set to two decimal places , you can see the result ,0.085=0.9 . It is normal in my sample .

My current desktop version is in May, maybe you can upgrade to the latest version to test the results .

https://www.microsoft.com/en-us/download/details.aspx?id=58494

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kentz]

Hi @v-yetao1-msft , thanks for your reply! Yes that rounding works. But the problem occures when using rounding in Power Query Editor and Dataflow. And confirming I'm using the latest client.