title: How do I calculate Percentiles in PowerQuery based on grouping variables?
body: <p>I have a few columns of data, I need to convert the excel version of "PERCENTILE" into Powerquery format. </p>
  
  <p>I have some code which adds in as a function but doesnt apply accurately as it doesnt allow for grouping of the data by CATEGORY and YEAR. So anything that is in Full Discretionary 1.5-2.5 AND 2014 needs to be added to the percentile array, equally anything that falls in Full discretionary 2.5-3.5 AND 2014 needs to go into a different percentile array</p>
  
  <pre><code>let
  
   Source = (list as any, k as number) =&gt; let
  
   Source = list,
  
   #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  
   #"Sorted Rows" = Table.Sort(#"Converted to Table",{{"Column1", Order.Ascending}}),
  
   #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1),
  
   #"Added Custom" = Table.AddColumn(#"Added Index", "TheIndex", each Table.RowCount(#"Converted to Table")*k/100),
  
   #"Filtered Rows" = Table.SelectRows(#"Added Custom", each [Index] &gt;= [TheIndex] and [Index] &lt;= [TheIndex]+1),
  
  Custom1 = List.Average(#"Filtered Rows"[Column1])
  
     in
  
   Custom1
  
  in
  
   Source
  </code></pre>
  
  <p>So Expected results would be that anything that matches off on the 2 columns (Year,Category) should be applied within the same array. Currently invoking the above function just gives me errors.
  I have also tried using grouping and outputting the "Min, Median, and Max" outputs but I also require 10% and 90% Percentiles.</p>
  
  <p>Thank you in advance</p>