Forum Discussion
Solved
percentile
Hi,
I have to create a custom column in the query performing the percentile based on a set of values in another column. Do you know any formula? I know how to do it in DAX but in this case I really need an additional column in the query!
saramark , there's no ready-to-eat percentile-wise functions available in PQ so far; but we can build some workarounds in this pattern,
//dataset is a list of numbers, pct ranges from 0 to 1 Pctl = (dataset as list, pct as number) => List.Max(List.MinN(dataset, Number.Round(List.Count(dataset)*pct, 0)))this user defined function can be included in a query and is easy to be called in an applied setp this way,
let //dataset is a list of numbers, pct ranges from 0 to 1 Pctl = (dataset as list, pct as number) => List.Max(List.MinN(dataset, Number.Round(List.Count(dataset)*pct, 0))), Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZBBDgQhCAT/4nkvAoK+xcz/vzETi03w1BG7Wtq9m1h7fruZHPGJWD1ZHJnryNAjcll61LvByQEE6YgCDHCZ1RlwwTAS8Jrig5UyWivQcS6qzExhaHCaVby+kG3/QCfldoKvXBeLwg2/fimLfcPnBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [val = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"val", type number}}), //call to fx Pctl #"75% Percentile" = Pctl(#"Changed Type"[val], .75) in #"75% Percentile"
1 Reply
saramark , there's no ready-to-eat percentile-wise functions available in PQ so far; but we can build some workarounds in this pattern,
//dataset is a list of numbers, pct ranges from 0 to 1 Pctl = (dataset as list, pct as number) => List.Max(List.MinN(dataset, Number.Round(List.Count(dataset)*pct, 0)))this user defined function can be included in a query and is easy to be called in an applied setp this way,
let //dataset is a list of numbers, pct ranges from 0 to 1 Pctl = (dataset as list, pct as number) => List.Max(List.MinN(dataset, Number.Round(List.Count(dataset)*pct, 0))), Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZBBDgQhCAT/4nkvAoK+xcz/vzETi03w1BG7Wtq9m1h7fruZHPGJWD1ZHJnryNAjcll61LvByQEE6YgCDHCZ1RlwwTAS8Jrig5UyWivQcS6qzExhaHCaVby+kG3/QCfldoKvXBeLwg2/fimLfcPnBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [val = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"val", type number}}), //call to fx Pctl #"75% Percentile" = Pctl(#"Changed Type"[val], .75) in #"75% Percentile"