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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      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 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