problem with foating number

[nicolast29]

Hi

I have to transert data to a SAAS financial software

This one contrôle day by day the total between 2 column who must be equal to zéro

For me due to the storage of the values with foating values it block

I read some article, thoses for example

https://solveandexcel.ca/2021/03/09/rounding-in-power-query-default-rounding-mode-and-the-binary-dec...

https://gorilla.bi/power-query/precision/

https://towardsdatascience.com/binary-representation-of-the-floating-point-numbers-77d7364723f1

And try different way with Power query, but i didin'' arrive to solde my way

If someone can help me, here the file

pb floating values.xlsx

[nicolast29]

thanks for replying, but it doesn't help, because i need to import each row, not the group by

Ands if you look at the result of your request i have the same problem

[ronrsnfld]

Well then you can use the pivot table added field formulas I provided to round or check for significant differences.

And since you seem to think that a difference of 0.01 is NOT significant, adjust the formulas to what you think is significant.

But note that the differences of 0.01 are real based on your original table in your workbook -- they are not a result of floating point inaccuracies. You'd need to go further back in the data chain to determine where that 0.01 difference is coming from.

[ronrsnfld]

Two ways of dealing with this issue are

1. Test for difference from zero and accept result as zero or not depending on the difference 
2. Round to the desired level of precision.

eg

    = IF(ABS(Solde2 ) > 0.0000000001,Solde2,0)

    = ROUND(Solde2,10)

Another method to obtain the same result, but without the Pivot Table, would be to Group and Sum in Power Query, using the Currency.Type for your data. eg:

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau2"]}[Content],
    #"Autres colonnes supprimées" = Table.SelectColumns(Source,{"EcritureDate", " Débit ", " Crédit "}),
    #"Type modifié" = Table.TransformColumnTypes(#"Autres colonnes supprimées",{{" Débit ", type text}, {" Crédit ", type text}}),
    #"Lignes filtrées" = Table.SelectRows(#"Type modifié", each ([#" Débit "] <> " -   ") and ([#" Crédit "] <> " -   ")),
    #"Type modifié1" = Table.TransformColumnTypes(#"Lignes filtrées",{{" Débit ", Currency.Type}, {" Crédit ", Currency.Type}}),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Type modifié1", "Débit2", each Value.Add([#" Débit "],0,Precision.Decimal),Currency.Type),
    #"Personnalisée ajoutée1" = Table.AddColumn(#"Personnalisée ajoutée", "Crédit2", each Value.Add([#" Crédit "],0,Precision.Decimal),Currency.Type),
    #"Soustraction insérée" = Table.AddColumn(#"Personnalisée ajoutée1", "Solde2", each [Débit2] - [Crédit2], Currency.Type),
    #"Grouped Rows" = Table.Group(#"Soustraction insérée", {"EcritureDate"}, {{"Somme de Solde2", each List.Sum([Solde2]), Currency.Type}})
in
    #"Grouped Rows"

[nicolast29]

another thanks but my expression wasn't good, let's try another way,

 my objectif is to import data from the sheet importjanvier only (because i need all the details)

you: Well then you can use the pivot table added field formulas I provided to round or check for significant differences.

Me: Ok, that point the pb, but didn't solve it because i have to import each row from importjanvier

you:   But note that the differences of 0.01 are real based on your original table in your workbook -- they are not a result of floating point inaccuracies. You'd need to go further back in the data chain to determine where that 0.01 difference is coming from.

Me: ok i wasn't enought precise, focus in my head with the other values

May be it doesnt come from the floating point inaccuracies

in case of a difference of 0.xx (from 1 to 2 digit) i will automatically create an row for compensation

Only the row with 0.xxyyyyzzzz  .... cause me trouble

[ronrsnfld]

I don't understand what you want.  It seems that what you posted is not what you really want.

In your workbook, you show a two column pivot table with floating point addition errors in many rows. Since this two column table is a pivot table, I showed you two formulas, either one of which you can use in a Pivot Field custom formula to correct that issue. 

I also discussed some other approaches to your problem, but the first should work given what you have posted in your question.

But no matter how you do it, the approach is the same -- you adjust the precision of your results to match what you require, or test for the acceptable variance from zero. (Setting the data type to Currency in PQ effectively sets the precision to four decimals).

If you need something else, you'll need to be more detailed.

By the way, there are other methods of dealing with this issue in Power Query. As well as methods to add in that balancing entry.