Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
cst_bi
Frequent Visitor

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
1 ACCEPTED SOLUTION
spinfuzer
Super User
Super User

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

 

View solution in original post

2 REPLIES 2
cst_bi
Frequent Visitor

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
Super User
Super User

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

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors