Forum Discussion
Rounding problem in both Power Query Editor and Dataflow on web
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?
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
4 Replies
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.
Hi Anonymous , 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.
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
CHAMP!! That worked. Thanks so much!
