Power Query decimal precision problem - does not get to 0 when negative values equal positive values

[yossifisch]

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

[Rickmaurinus]

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.

[Anonymous]

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.

Number.Round

Then calculate with 'round up' values and they will get to 0 when negative values equal positive.

Regards,

Xiaoxin Sheng

[yossifisch]

@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.

[Anonymous · Accepted solution]

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

[yossifisch]

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?

[Anonymous]

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

[Anonymous]

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