Whole number conversion issue in Power Query

[Anonymous]

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 then it needs to Round up like highlighted in red color.

Result , i need like below in Query editor itself.

Thanks,

@amitchandak@Greg_Deckler

[ronrsnfld]

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"