Forum Discussion
Power Query decimal precision problem - does not get to 0 when negative values equal positive values
Hi yossifisch,
I got the feedback from power query team.
They suggest you to use precision argument with List.Sum, it can fix this issue.
Sample:
List.Sum([amount], Precision.Decimal)
Full query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjJV0lEyNNQzMFGK1YFyddH4pnqmRkiyqFwgzxCfJF6dxuQZi829xvilCXknFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [order = _t, amount = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"order", Int64.Type}, {"amount", type number}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"order"}, {{"Sum", each List.Sum([amount], Precision.Decimal)}}) in #"Grouped Rows"Regards,
Xiaoxin Sheng
Anonymous That doesn't work (and BTW I think setting to Currency type already takes care of rounding).
Sample data:
| order | amount |
| 25 | 11.04 |
| 25 | -11.04 |
| 25 | 5.52 |
| 25 | -5.52 |
| 25 | 5.51 |
| 25 | -5.52 |
| 25 | 5.52 |
| 25 | -5.52 |
| 25 | 5.53 |
| 25 | -5.52 |
| 25 | 5.51 |
| 25 | -5.52 |
| 25 | 11.04 |
| 25 | -11.03 |
| 25 | 11.04 |
| 25 | -11.04 |
| 25 | 5.52 |
| 25 | -5.52 |
Sample Power Query code:
let
Source = Csv.Document(File.Contents("C:\Users\ABC\Documents\Book1.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"order", type text}, {"amount", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Number.Round([amount], 2)),
#"Grouped Rows" = Table.Group(#"Added Custom", {"order"}, {{"Sum", each List.Sum([Custom]), type number}})
in
#"Grouped Rows"Result data:
| order | Sum |
| 25 | 1.78E-15 |
Again, this is a tiny number that is not noticed when summarized in a pivot table with formattinge set to currency but in my large data set this resulted in a $4 discrepancy.
HI yossifisch,
I reproduce your issue, it seems like list has calculate wrong result when it deal with values which has different symbol(negative /positive) and same absolute.
I Will contact to power bi team to confirm this issue and update here if any feedback.
Regards,
Xiaoxin Sheng