Forum Discussion

Kentz's avatar
Kentz
Regular Visitor
5 years ago
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?
Need Help
  • KNP's avatar
    KNP
    5 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Kentz 

Is the column you set up as a regular numeric column or is it generated by dax ? And what’s your datasource ?

I enter data in desktop directly and set to two decimal places , you can see the result ,0.085=0.9 . It is normal in my sample .


My current desktop version is in May, maybe you can upgrade to the latest version to test the results .

https://www.microsoft.com/en-us/download/details.aspx?id=58494

 

Best Regards

Community Support Team _ Ailsa Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Kentz's avatar
    Kentz
    Regular Visitor
    5 years ago

    Hi Anonymous , thanks for your reply! Yes that rounding works. But the problem occures when using rounding in Power Query Editor and Dataflow. And confirming I'm using the latest client.