percentile

[saramark]

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!

[CNENFRNL · Accepted solution]

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