Forum Discussion

yossifisch's avatar
yossifisch
Advocate I
8 years ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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's avatar
yossifisch
Advocate I
8 years ago

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's avatar
Anonymous
Not applicable
8 years ago

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