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 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
Thanks Anonymous, this works but what if I don't want to summarize using groups but rather an Excel Pivot Table (as in my original post), is there a way to set the Pivot Table's behavior to use Precision.Decimal?
HI yossifisch,
I'm not so familiar with pivot table, maybe you can post to power pivot related forum to get further support.
Regards,
Xiaoxin Sheng