Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
id | val | k |
a | 2 | a1 |
b | 2 | a1 |
a | 8 | a1 |
a | null | a1 |
b | 4 | a1 |
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"
id | val | k |
a | 2 | a2 |
b | 2 | a2 |
a | 8 | a2 |
a | null | a2 |
b | 4 | a2 |
let
Source = Table.Combine({a1,a2}),
#"Grouped rows" = Table.Group(Source, {"id", "k"}, {{"avg", each List.Average([val]), type nullable number}})
in
#"Grouped rows"
id | k | avg |
a | a1 | 3.3333333333333335 |
b | a1 | 3 |
a | a2 | 3.3333333333333335 |
b | a2 | 3 |
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"
id | avg |
a | 5 |
b | 3 |
Solved! Go to 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}})
Hi @spinfuzer ,
Thank you
And references to :
https://gorilla.bi/power-query/precision/
Maybe we should always add optional parameter Precision.Decimal for each list function 😅
Sincerely,
Oviedityanto
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}})
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.