POWER QUERY - Group by - wrong SUM

Question

Hello,
I have encountered a very strange behavior - GROUP BY is not summing numbers correctly:

I have this source table:

And I want to group data by ID, mesic and rok, and SUM FTE values.
But for 2021 and mesic=2 I am getting this weird number in power query preview:

Also when I add a custom column with formula [FTE]=1, the result is FALSE.

Here is a complete query:

let  
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],  
    #"Changed Type1" = Table.TransformColumnTypes(Source,{<!-- -->{"PODNIK", Int64.Type}, {"Entita", Int64.Type}, {"ID", Int64.Type}, {"mesic", Int64.Type}, {"rok", Int64.Type}, {"jmeno", type text}, {"Vynětí z ES", type any}, {"FTE", type number}}),  
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"ID", "mesic", "rok"}, {<!-- -->{"FTE", each List.Sum([FTE]), type nullable number}}),  
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "is1", each [FTE]=1)  
in  
    #"Added Custom"  

The result query output show "1", but the custom column still shows FALSE.
I have tried to manually retype the values in the source table, but it did not help.

Any Ideas why this happens? it is quite a problem, because the value is just wrong- even if it looks as "1" in excel, custom columns are affected by the wrong calculation.

Are you getting the same results when you duplicate my source table?
Any ideas?

EDIT:
I have just found out that it is dependent on the sort:
When I sort FTE asc.:

When I sort FTE desc.:

Thanks
Jakub

Answer 1

Hello,
I have received an explanation from the Power Query developer:
It is rather a feature than a bug -"It’s the beauty of floating-point arithmetic"
Using List.Sum([FTE], Precision.Decimal) should solve the problem.

I have also found other threads that are about the same "feature".

One of them is:
https://community.powerbi.com/t5/Issues/Strange-behaviour-when-deducting-decimal-numbers-precision/idc-p/578372#M35775

Answer 2

Hi @Jakub Dusek

As a temp. workaround could you check the following does it in your context?

Create a new query with the following code and name it i.e. fxCustomSum

(iTable as table, cName as text) as nullable number =>  
let  
    Source = Table.Column(iTable, cName),  
    Sum = List.Sum(  
        List.Transform(Source, each _ +1)  
    )  
in  
    Sum - List.NonNullCount(Source)  

Call it as follow:

GroupedRows = Table.Group(<PreviousStep>, {"ID", "mesic", "rok"},  
    {"FTE", each fxCustomSum(_, "FTE"), type number}  
)

Answer 3

This won't really help you but this doesn't seem to be a Grouping nor a List.Sum issue

let  
    Source = {0.2,0.1,0.7 ,0.7,0.2,0.1},  
    FirstThree = List.FirstN(Source, 3),  
    LastThree = List.LastN(Source, 3),  
  
    ListSum_FirstThree = List.Sum(FirstThree),          // 1  
    ListSum_LastThree = List.Sum(LastThree),            // 0.99999999999999989  
  
    ListAccum_FirstThree = List.Accumulate(FirstThree, 0,  
        (state,current)=> state + current  
    ),                                                  // 1  
    ListAccum_LastThree = List.Accumulate(LastThree, 0,  
        (state,current)=> state + current  
    ),                                                  // 0.99999999999999989  
  
    ListGen_FirstThree = List.Last(  
        List.Generate(()=> [i=0, out=List.First(FirstThree)],  
            each [i] < List.Count(FirstThree),  
            each [i = [i]+1, out = [out]+FirstThree{i}],  
            each [out]  
        )  
    ),                                                  // 1  
    ListGen_LastThree = List.Last(  
        List.Generate(()=> [i=0, out=List.First(LastThree)],  
            each [i] < List.Count(LastThree),  
            each [i = [i]+1, out = [out]+LastThree{i}],  
            each [out]  
        )  
    )                                                  // 0.99999999999999989  
in  
    ListGen_LastThree  

but

let  
// Order of the last 3 items changed,  
    Source = {0.2,0.1,0.7 ,0.7,0.1,0.2},  
    LastThree = List.LastN(Source, 3),  
  
    ListSum_LastThree = List.Sum(LastThree),            // 1  
    ListSum_EqualOne = ListSum_LastThree = 1,           // TRUE  
  
    ListAccum_LastThree = List.Accumulate(LastThree, 0,  
        (state,current)=> state + current  
    ),                                                  // 1  
    ListAccum_EqualOne = ListAccum_LastThree = 1,       // TRUE  
  
    ListGen_LastThree = List.Last(  
        List.Generate(()=> [i=0, out=List.First(LastThree)],  
            each [i] < List.Count(LastThree),  
            each [i = [i]+1, out = [out]+LastThree{i}],  
            each [out]  
        )  
    ),                                                  // 1  
    ListGen_EqualOne = ListGen_LastThree = 1            // TRUE  
in  
    ListGen_EqualOne  

With the previous observation I changed the order of the last 2 records in my SourceTable. Query code:

let  
    Source = Excel.CurrentWorkbook(){[Name="SourceTable"]}[Content],  
    #"Changed Type" = Table.TransformColumnTypes(Source,  
        {{"ID", Int64.Type}, {"mesic", Int64.Type}, {"rok", Int64.Type}, {"FTE", type number}}  
    ),  
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "mesic", "rok"},  
        {{"FTE", each List.Sum([FTE]), type nullable number}}  
    ),  
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "FTE equal 1", each  
        [FTE] = 1, type logical  
    )  
in  
    #"Added Custom"  

Answer 4

Hello,

Yes, the example could be even more simplified:

with Changed order:

So the basic "addition" function is not working properly? That's pretty disturbing

Do you all get this wrong result? Could you try it?

Warm regards,
Jakub