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

 

 

Result , i need like below in Query editor itself.

 

Thanks,

amitchandak@Greg_Deckler

 

Need Help
Show and Tell
Tips and Tricks

1 Reply

  • 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"