Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
yossifisch
Advocate I
Advocate I

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
1 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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

7 REPLIES 7
Rickmaurinus
Helper V
Helper V

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.

v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@v-shex-msft That doesn't work (and BTW I think setting to Currency type already takes care of rounding).

 

Sample data:

orderamount
2511.04
25-11.04
255.52
25-5.52
255.51
25-5.52
255.52
25-5.52
255.53
25-5.52
255.51
25-5.52
2511.04
25-11.03
2511.04
25-11.04
255.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:

 

orderSum
251.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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thanks @v-shex-msft, 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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.