none
Can the "Percentile" be calculated in Power Query? RRS feed

  • Question

  • Hi,

    I'm tring to achieve the following "Percentile" function using Power Query.

    Is there any possible to realize these?

    Thanks.

    Regards,

    Monday, January 5, 2015 1:49 AM

Answers

  • You can accomplish the second case by grouping in the UI and then editing the M script. For example (and to make editing easier), group country by sum & average of Sales (Home-->Group By). In the Advanced Editor, you will have a script line that looks like:

    Table.Group(RenamedColumns, {"Country"}, {{"Sales Total", each List.Sum([Amount Sales]), type number}, {"95 Percentile Sales", each List.Average([Amount Sales]), type number}})

    In the above formula, RenamedColumns is the name of the previous step in the script. Change the name to match your actual case. I've assumed that the pre-grouping sales amount column is "Amount Sales." Names of grouped columns are "Sales Total" and "95 Percentile Sales."

    Next modify the group formula, substituting List.Average with PercentileInclusive:

    Table.Group(RenamedColumns, {"Country"}, {{"Sales Total", each List.Sum([Amount Sales]), type number}, {"95 Percentile Sales", each PercentileInclusive(RenamedColumns[Amount Sales],0.95), type number}})

    Tuesday, January 6, 2015 1:04 PM

All replies

  • Power Query doesn't have a percentile function. You can load the data to the Data Model and then use PowerPivot to calculate percentiles. Read more here

    • Proposed as answer by Gil RavivMVP Monday, January 5, 2015 12:12 PM
    • Unproposed as answer by Gil RavivMVP Wednesday, January 7, 2015 3:35 PM
    Monday, January 5, 2015 7:01 AM
  • The following M functions were built using similar methods that were used in creating the custom PowerPivot percentile functions:

    //PercentileInclusive Function
    (inputSeries as list, percentile as number) =>
    let
        SeriesCount = List.Count(inputSeries),
        PercentileRank = percentile*(SeriesCount-1)+1, //percentile value between 0 and 1
        PercentileRankRoundedUp = Number.RoundUp(PercentileRank),
        PercentileRankRoundedDown = Number.RoundDown(PercentileRank),
        Percentile1 = List.Max(List.MinN(inputSeries,PercentileRankRoundedDown)),
        Percentile2 = List.Max(List.MinN(inputSeries,PercentileRankRoundedUp)),
        Percentile = Percentile1+(Percentile2-Percentile1)*(PercentileRank-PercentileRankRoundedDown)
    in
        Percentile   

    //PercentileExclusive Function
    (inputSeries as list, percentile as number) =>
    let
        SeriesCount = List.Count(inputSeries),
        PercentileRank = percentile*(SeriesCount+1), //percentile value between 0 and 1
        PercentileRankRoundedUp = Number.RoundUp(PercentileRank),
        PercentileRankRoundedDown = Number.RoundDown(PercentileRank),
        Percentile1 = List.Max(List.MinN(inputSeries,PercentileRankRoundedDown)),
        Percentile2 = List.Max(List.MinN(inputSeries,PercentileRankRoundedUp)),
        Percentile = Percentile1+(Percentile2-Percentile1)*(PercentileRank-PercentileRankRoundedDown)
    in
        Percentile


    Monday, January 5, 2015 7:18 PM
  • Thanks for your kind reply. But I'm still wandering is there any possilbe way to realize a percentile calculation based on a break column as the second figure showed.

    Could you please give me some hints of that? Sorry for the request.

    Thanks again.

    Regards,

    Tuesday, January 6, 2015 1:47 AM
  • Thanks for your kind reply. But I'm still wandering is there any possilbe way to realize a percentile calculation based on a break column as the second figure showed.

    Could you please give me some hints of that? Sorry for the request.

    Thanks again.

    Regards,

    Tuesday, January 6, 2015 1:48 AM
  • You can accomplish the second case by grouping in the UI and then editing the M script. For example (and to make editing easier), group country by sum & average of Sales (Home-->Group By). In the Advanced Editor, you will have a script line that looks like:

    Table.Group(RenamedColumns, {"Country"}, {{"Sales Total", each List.Sum([Amount Sales]), type number}, {"95 Percentile Sales", each List.Average([Amount Sales]), type number}})

    In the above formula, RenamedColumns is the name of the previous step in the script. Change the name to match your actual case. I've assumed that the pre-grouping sales amount column is "Amount Sales." Names of grouped columns are "Sales Total" and "95 Percentile Sales."

    Next modify the group formula, substituting List.Average with PercentileInclusive:

    Table.Group(RenamedColumns, {"Country"}, {{"Sales Total", each List.Sum([Amount Sales]), type number}, {"95 Percentile Sales", each PercentileInclusive(RenamedColumns[Amount Sales],0.95), type number}})

    Tuesday, January 6, 2015 1:04 PM
  • This is super useful.  I found this to be much more straightforward than all the confusing home-brew DAX flavors of PECENTILE.INC floating around on the web.

    I wish the Power Query team would add this as a built-in alongside things like List.Covariance.   It would be great if they could flesh out the mathematical capabilities.

    Thanks,
    Cliff Dibble

    CTD

    Saturday, January 20, 2018 8:38 PM
  • Hello,

    I agree on the approach, I'd like to be able to use it in Power Query directly.

    I did as suggested by  Ehren - MSFT and it works. The only thing that I'm noticing, is that the Power Query version, gives me (in several cases) different values than DAX, while using the same data set.

    What can I be doing wrong?

    NOTE: Due to validation, I'll be able to show a sample pic until my account is confirmed

    Thanks


    DAX Measure

        Percentile 90 = PERCENTILE.INC([OTD - Resolution Duration (Hrs)],0.90)


    POWER QUERY code for the Group By

        #"Grouped Rows" = Table.Group(#"Reordered Columns", {"Seniority", "Priority"}, {{"90 Percentile     Resoultion Duration", each PercentileInclusive([#"OTD - Resolution Duration (Hrs)"],0.90), type number}})
    Tuesday, July 24, 2018 6:58 PM
  • When applying this change in the M script, I receive the errror "The name '...' wasn't recognized. Make sure it's spelled correctly."
    Tuesday, December 4, 2018 2:29 PM