Forum Discussion
Solved
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
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!