Forum Discussion

bartvana's avatar
bartvana
Iron Contributor
Sep 11, 2021
Solved

Percentiles from frequency table

Hello, This is a quite common problem to solve in statistics it seems to me, but I can't find an easy solution in Excel. Am I missing something or is this just something that's not incorporated? Th...
BI & Data Analysis
Formulas and Functions
power bi
  • SergeiBaklan's avatar
    SergeiBaklan
    Sep 11, 2021

    bartvana 

    You may group by Product without aggregation and apply former procedure to each group

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(
        Source, {"Product"},
        {{"Percentile",
                each
                [
                    allValues = Table.AddColumn(
                    _ ,
                    "Lists",
                    each List.Numbers([Value],[Frequency],0)
                    ),
                    Percentile = List.Percentile (
                        List.Combine(allValues[Lists]), 0.5
                    )                
                ][Percentile]
        }}
    )
in
    #"Grouped Rows"

    or use function instead

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    fnPercentile = (tbl as table) => 
        let
            allValues = Table.AddColumn(
                tbl , "Lists",
                    each List.Numbers([Value],[Frequency],0)
            ),
            Percentile = List.Percentile (
                List.Combine(allValues[Lists]), 0.5
            )                
        in
            Percentile,

    #"Grouped Rows" = Table.Group(
        Source, {"Product"},
        {{ "Percentile", each fnPercentile(_) }}
    )
in
    #"Grouped Rows"

    Here the only we assume predefined column names in functions.

     

    I'd filter Frequency = 0 before grouping.

    In attached file is above and "Excel variant".

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 11, 2021

bartvana 

Not sure about the math, but it's easy to repeat Excel sample. For such table

script which returns median (or 0.5 percentile) is

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    allValues = Table.AddColumn(
        Source, "Lists",
        each List.Numbers([Value],[Frequency],0)),
    Percentile = List.Percentile(            // or List.Median
        List.Combine(allValues[Lists]), 0.5)
in
    Percentile

Performance could be an issue, but that's only to play with actual. Perhaps fixing in memory with Index column or Table.Buffer() could help a bit. Or wrap above with List.Buffer().

 

bartvana's avatar
bartvana
Iron Contributor
Sep 11, 2021

SergeiBaklan That's genius!
I'm trying to implement this in my real life data and have only one problem, the grouping by product. Imagine you would have sample data like this and want to calculate the median by product (see also attached workbook where I added the column):

 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Sep 11, 2021

    bartvana 

    You may group by Product without aggregation and apply former procedure to each group

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(
        Source, {"Product"},
        {{"Percentile",
                each
                [
                    allValues = Table.AddColumn(
                    _ ,
                    "Lists",
                    each List.Numbers([Value],[Frequency],0)
                    ),
                    Percentile = List.Percentile (
                        List.Combine(allValues[Lists]), 0.5
                    )                
                ][Percentile]
        }}
    )
in
    #"Grouped Rows"

    or use function instead

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    fnPercentile = (tbl as table) => 
        let
            allValues = Table.AddColumn(
                tbl , "Lists",
                    each List.Numbers([Value],[Frequency],0)
            ),
            Percentile = List.Percentile (
                List.Combine(allValues[Lists]), 0.5
            )                
        in
            Percentile,

    #"Grouped Rows" = Table.Group(
        Source, {"Product"},
        {{ "Percentile", each fnPercentile(_) }}
    )
in
    #"Grouped Rows"

    Here the only we assume predefined column names in functions.

     

    I'd filter Frequency = 0 before grouping.

    In attached file is above and "Excel variant".

    • bartvana's avatar
      bartvana
      Iron Contributor
      Sep 11, 2021

      SergeiBaklan You've been of great help, thank you! I managed to do it.

      I like to use the Power Query UI as much as possible, and also go in gradual steps so I can understand afterwards what I did, so I ended up with this to make the lists:

      let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Value", Int64.Type}, {"Frequency", Int64.Type}}),
    //List per Value
    #"Added Custom" = Table.AddColumn(#"Changed Type", "ListPerValue", each List.Numbers([Value], [Frequency], 0), type list),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Frequency"}),
    //List per product (combine lists per value for each product)
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Product"}, {{"ListPerProduct", each List.Combine([ListPerValue]), type list}}),
    //Show list to check
    #"Extracted Values" = Table.TransformColumns(#"Grouped Rows", {"ListPerProduct", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    #"Extracted Values"

      First a simple "Add custom column", with the List.Number function.

      Then the grouping by doing a generic sum grouping using the UI, then changing the sum function into List.Combine as you showed. The last step is just for checking the result.

      Thanks again!

      (Workbook attached for future reference).

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Sep 11, 2021

        bartvana you are welcome, glad to help.

         

Resources