average calculation in power query

Jeanxyz,

 

An alternate approach using Group By is to add an aggregation for all rows:

 

1. Group By

 

 

 

2. Expand the All column and select Date Key and FTE rate:

 

 

3. Result:

 

 

Hi Jeanxyz ，

Base data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc5LCgAgCEXRvTgO0teH9hLtfxs5zXw6EQ7C3VvUx6QIFFZ9/TRVOeUj+LlSafypcxqcZiDwQLyBKcQ+8D7wPiR95wI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"employee id" = _t, #"Date Key" = _t, #"FTE rate" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"employee id", Int64.Type}, {"Date Key", type date}, {"FTE rate", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"employee id"}, {{"average", each List.Average([FTE rate]), type nullable number}, {"TABLE1", each _, type table [employee id=nullable number, Date Key=nullable date, FTE rate=nullable number]}}),
    #"Expanded TABLE1" = Table.ExpandTableColumn(#"Grouped Rows", "TABLE1", {"employee id", "Date Key", "FTE rate"}, {"TABLE1.employee id", "TABLE1.Date Key", "TABLE1.FTE rate"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded TABLE1",{"employee id", "TABLE1.employee id", "TABLE1.Date Key", "TABLE1.FTE rate", "average"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"average", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"employee id"})
in
    #"Removed Columns"

Final get:

 

Wish it is helpful for you!

 

Best Regards

Lucien