Forum Discussion
Power Query decimal precision problem - does not get to 0 when negative values equal positive values
I have source data with over a million rows with a dollar amount in each. A large percentage of those amounts cancel each other out, so there is the same amount in negative value as in positive. The problem is that Power Query does not sum those cancelled-out values as 0, instead it makes some tiny fractional number which results in a few dollars discrepancy when I summarize all million+ rows.
The column is formatted as Currency.
For example: When I summarize the table below in a Pivot Table I get 1.77636E-15 instead of 0. Again, this isn't noticable when you format it as Currency in the Pivot Table (because of rounding) but when you have thousands of such scenarios it actually makes a difference in the final number.
Amount
| 11.04 |
| -11.04 |
| 5.52 |
| -5.52 |
| 5.51 |
| -5.52 |
| 5.52 |
| -5.52 |
| 5.53 |
| -5.52 |
| 5.51 |
| -5.52 |
| 11.04 |
| -11.03 |
| 11.04 |
| -11.04 |
| 5.52 |
| -5.52 |
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
7 Replies
The precision parameter is useful here. To get a more precise number you can use Decimal.Precision. By default Power Query uses Double.Precision. More on the topic you can find here:
Power Query Precision: Avoid Rounding Errors - BI Gorilla
--------------------------------------------------
@ me in replies or I'll lose your thread
Master Power Query M? -> https://powerquery.how
Read in-depth articles? -> BI Gorilla
Youtube Channel: BI Gorilla
If this post helps, then please consider accepting it as the solution to help other members find it more quickly.
HI yossifisch,
If you not care about value after huge amount of decimal places, I'd like to suggest you add custom column with Number.Round function to round these value.
Then calculate with 'round up' values and they will get to 0 when negative values equal positive.
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
