Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries