New Percentiles Transformation in Power Query
This content applies to: Power Query online
There’s a new operation 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:
One last thing to mention is that this operation is foldable (that is capable of query folding). Some examples of data sources that can fold this operator are SQL Server and Azure Data Explorer.