Forum Discussion

bankit710's avatar
bankit710
Regular Visitor
8 years ago

Rounding decimal numbers

i have found a problem with ROUND function.   what i was doing is as follows i have one price column, one quantity column in my table i am getting data from dynamics RMS where at the POS, custo...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

If this is M (power query) then you need to add an additional parameter in the Advanced Editor:

 

https://msdn.microsoft.com/en-us/library/mt253380.aspx

 

According to that article, it is applying "If roundingMode is not specified, RoundingMode.ToEven is used." which is kind of a stupid default, I'll give you that. To fix, see below:

 

See this code:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQzMjNVitWJVjLWM7GAsIBixhCWqZ6ZJYRlomdqhmCZwFnmSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Unrounded = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unrounded", type number}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Unrounded", "Unrounded - Copy"),
    #"Rounded Off" = Table.TransformColumns(#"Duplicated Column",{{"Unrounded - Copy", each Number.Round(_, 2), type number}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Rounded Off", "Unrounded", "Unrounded - Copy.1"),
    #"Rounded Off1" = Table.TransformColumns(#"Duplicated Column1",{{"Unrounded - Copy.1", each Number.Round(_, 2, 0), type number}})
in
    #"Rounded Off1"

The second round returns tie-breakers of 5 rounding up.