Invalid List.Average Value when Grouping with Table.Combine

[cst_bi]

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 :

id	val	k
a	2	a1
b	2	a1
a	8	a1
a	null	a1
b	4	a1

'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 :

id	val	k
a	2	a2
b	2	a2
a	8	a2
a	null	a2
b	4	a2

'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 :

id	k	avg
a	a1	3.3333333333333335
b	a1	3
a	a2	3.3333333333333335
b	a2	3

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 :

id	avg
a	5
b	3

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

[cst_bi]

Hi @spinfuzer ,

Thank you

And references to :

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

https://community.fabric.microsoft.com/t5/Desktop/Power-Query-decimal-precision-problem-does-not-get...

Maybe we should always add optional parameter Precision.Decimal for each list function 😅

Sincerely,

Oviedityanto

[spinfuzer · Accepted solution]

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}})