[This post was originally published on the Power Query blog which no longer exists. I have republished my posts from there here to make sure the content remains available.]
There’s a new aggregation option available for the Group By transformation in Power Query Online: percentiles. To see how it works, let’s see a simple example.
Consider the following Excel table containing information about invoices from different countries:
If you connect to this table from a Power BI or Power Platform dataflow, you can select the Country column and click the Group by button on the Transform tab in the ribbon:
When you do this the Group By dialog will open with the Country column selected in the Group by dropdown box. This is where you’ll see the new Percentile option in the Operation dropdown box; if you select it another box will appear underneath the Operation dropdown box where you can enter a number between 0 and 100 to indicate which percentile should be returned when the data is aggregated. You will also need to enter a new column name to contain the aggregated data and select a column containing the numeric data to be aggregated. Here’s what the Group By dialog would look like if you wanted to find the value for the 50th percentile from the Amount column for each country:
Click OK and here’s the output of the transformation:
This functionality uses the List.Percentile M function to do its work.
One last thing to mention is that if you’re using SQL Server or Azure Data Explorer as your data source query folding will take place for this transformation.