Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Whole number conversion issue in Power Query

Hi,   Please find the below, while converting  the Decimal to whole number - sometimes round up is working and sometimes round down . Example- For  62.5 and 61.5 showing same value. If 0.5 come t...
Need Help
Show and Tell
Tips and Tricks
ronrsnfld's avatar
ronrsnfld
Super User
2 years ago

How are you converting it?

 

It seems that if you just set the data type to "Int64.Type" the system will round to the nearest even number (so-called banker's rounding although I don't think it was used by bankers).

 

That rounding method actually decreases the variance compared with always round 0.5 up.

 

If you want to change the rounding method from the default, you can accomplish the conversion using `Int64.From` and specify your desired rounding method.

For example:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtAzVYrViVYyhNImUNrMCMaAyZgZ65koxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Test = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Test", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Result", each Int64.From([Test],"en-US",RoundingMode.AwayFromZero), Int64.Type)
in
    #"Added Custom"