Geometric Mean for Grouped Data

[WaiamMalibari]

Hello Team,

Could you please help me with this I want to get the GeoMean for below example. I have groped data and I want to creat a cutom column that calculate a the Geometric Mean for the Duration. I want to creat the column inside the power query not do it AS MEASURE or COLUMN on dax. below table showning the data that are grouped by and I calculate the Geomean on excel so How I can do it in the same way in the Power Query any idea ? or Formula it can help me please?

Direction	Job Type	Conveyance	Field Name	Base	Duration	Geo Mean
UP	OH	Wireline	AAA	OIL	1.5	3.170662853
UP	OH	Wireline	AAA	OIL	2.5	3.170662853
UP	OH	Wireline	AAA	OIL	8.5	3.170662853
DOWN	VSP	Drillpipe	BBB	Water	1.5	4.181723304
DOWN	VSP	Drillpipe	BBB	Water	6.5	4.181723304
DOWN	VSP	Drillpipe	BBB	Water	7.5	4.181723304
UP	FTS	Wireline	CCC	OIL	2.5	3.708099244
UP	FTS	Wireline	CCC	OIL	5.5	3.708099244

Thank you in Advance for helping

[ImkeF · Accepted solution]

Hi @WaiamMalibari ,
I would recommend to do a grouping using the UI with a dummy aggregation and then replace it with the formula for the geometric mean: 

Number.Power(List.Product([Duration]), 1 / List.Count([Duration]))

Please paste the following code into the advanced editor and follow the steps:

let
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "i45WCg1Q0lHy9wAS4ZlFqTmZealApqOjI0jU0wdIGuqZKsXqEKHQiFiFFlCFLv7hfkBuWDBIg0tRZk5OQWYBSLGTkxNId2JJahGSA4hUbkaacnNkV7uFBKM629nZGbv/8Ko0BamMBQA=",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ((type nullable text) meta [Serialized.Text = true])
        in
            type table [
                Direction = _t,
                #"Job Type" = _t,
                Conveyance = _t,
                #"Field Name" = _t,
                Base = _t,
                Duration = _t
            ]
    ),
    #"Changed Type" = Table.TransformColumnTypes(
        Source,
        {
            {"Direction", type text},
            {"Job Type", type text},
            {"Conveyance", type text},
            {"Field Name", type text},
            {"Base", type text},
            {"Duration", type number}
        }
    ),
    #"Grouped Rows" = Table.Group(
        #"Changed Type",
        {"Direction", "Job Type", "Conveyance", "Field Name", "Base"},
        {
            {
                "Geo Mean",
                each Number.Power(List.Product([Duration]), 1 / List.Count([Duration])),
                type nullable number
            },
            {"Partition", each _}
        }
    ),
    #"Expanded Partition" = Table.ExpandTableColumn(
        #"Grouped Rows",
        "Partition",
        {"Duration"},
        {"Duration"}
    )
in
    #"Expanded Partition"​