average calculation in power query

[Jeanxyz]

Hi,

I have a list of employee's FTE rate   (work %) by month. I would like to calculate the average FTE rate (result should look like column D) per employee in power query. how should I write the power query formula?

[DataInsights · Accepted solution]

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

[v-luwang-msft · Accepted solution]

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

[DataInsights · Accepted solution]

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

[amitchandak]

@Jeanxyz , You might have create a table using group by avg and join it back with this table

Using group by option, Employee ID as Group and Avg of FTE rate , create a new table. Merge it back using merge option using employee id.

Also refer for part of the approch here

https://www.youtube.com/watch?v=EFQBMJ6JyCQ