Forum Discussion

cst_bi's avatar
cst_bi
Helper I
2 years ago
Solved

Invalid List.Average Value when Grouping with Table.Combine

Hi Experts,

 

I want to combine table using Table.Combine from 1 table 'a1' and 'a2'

After table combined, List.Average is used to calculate the average value but it result invalid value

Here is the example :

 

'a1' table :

let

  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIC4kRDpVidaKUkVC5I1gKVm1eak4Oq3gTKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, val = _t, k = _t]),

  #"Changed column type" = Table.TransformColumnTypes(Source, {{"id", type text}, {"val", Int64.Type}})

in

  #"Changed column type"
 
the table 'a1' content :
idvalk
a2a1
b2a1
a8a1
anulla1
b4a1
 
'a2' table :
let

  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIC4kQjpVidaKUkVC5I1gKVm1eak4Oq3gTKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, val = _t, k = _t]),

  #"Changed column type" = Table.TransformColumnTypes(Source, {{"id", type text}, {"val", Int64.Type}})

in

  #"Changed column type"
 
the table 'a2' content :
idvalk
a2a2
b2a2
a8a2
anulla2
b4a2
 
'combine' table :
let

  Source = Table.Combine({a1,a2}),

  #"Grouped rows" = Table.Group(Source, {"id", "k"}, {{"avg", each List.Average([val]), type nullable number}})

in

  #"Grouped rows"
 
the table 'combine' content :
idkavg
aa13.3333333333333335
ba13
aa23.3333333333333335
ba23
 
Why in 'id' = 'a' and 'k' = 'a1' the value in 'avg' column is calculation from 10/3 ? I think it should be 10/2 = 5 because there's 1 null row
Of course we can replace List.Average with formula List.Sum([value]) / List.NonNullCount([value]) to return the correct value
But still I don't understand why List.Average doesn't work well
 
The case below is an example for correct value of List.Average 
 
Using 'a1' table without Table.Combine function return the correct value as show below
let

  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIC4kRDpVidaKUkVC5I1gKVm1eak4Oq3gTKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, val = _t, k = _t]),

  #"Changed column type" = Table.TransformColumnTypes(Source, {{"id", type text}, {"val", Int64.Type}}),

  #"Grouped rows" = Table.Group(#"Changed column type", {"id"}, {{"avg", each List.Average([val]), type nullable number}})

in

  #"Grouped rows"
 
the table output :
idavg
a5
b3
 
These table output above is what we need when using Table.Combine with List.Average
I hope Power BI team should be consider about this case
 
Sincerely,
Oviedityanto
Need Help
Show and Tell
  • spinfuzer's avatar
    spinfuzer
    2 years ago

    Try a Table.Buffer() on the Source.

     

    Source = Table.Buffer(Table.Combine({a1,a2})),

    Oddly, if you don't use Table.Buffer and use the optional argument of List.Average you get the correct answer as well.

     

     #"Grouped rows" = Table.Group(Source, {"id", "k"}, {{"avg", each List.Average([val], Precision.Decimal), type nullable number}})

     

2 Replies