Power Query offers a few ways to perform advanced aggregations like percentiles and custom calculations:

1. Group By with Built-in Aggregations:

* Percentile: (Available in Power Query Online)
* This is the simplest method for calculating percentiles.
* In the "Transform" tab, select "Group By".
* Choose the column to group by and select "Percentile" from the "Operation" dropdown.
* Specify the percentile value (between 0 and 100) and a new column name for the result.

2. Group By with Custom M Code:

* This method offers more flexibility for custom calculations beyond built-in functions.
* Group your data by the desired column(s) using "Group By".
* In the "Advanced" tab, expand "Custom Column".
* Here, you can write M code using List functions or other M operations to perform your custom calculation on the grouped data.

Example (Calculating Average Sales per Country with Minimum Order Value):

```
= Table.AddColumn(
SourceTable,
"Average Sales (Min Order $100)",
List.Average(
Var{CurrentGroup} = CurrentGroup,
if [Order Value] >= 100, [Sales], 0)
)
```

3. Custom Columns without Grouping:

* This approach is useful for creating new calculated columns based on existing data without grouping.

Example (Adding a "Price Difference" Column):

```
= Table.AddColumn(
SourceTable,
"Price Difference",
[Regular Price] - [Discounted Price]
)
```

Additional Tips:

* Explore the available M functions like List.Percentile, List.Min, List.Max, etc., for various aggregation needs [https://learn.microsoft.com/en-us/powerquery-m/power-query-m-function-reference](https://learn.microsoft.com/en-us/powerquery-m/power-query-m-function-reference).
* Consider using helper functions to break down complex calculations into smaller, reusable functions.
* For very complex calculations, creating a separate custom function in Power BI might be a better option.

Remember, the best approach depends on the specific aggregation you need. By combining these techniques and leveraging M functions, you can perform a wide range of advanced aggregations and custom calculations in Power Query.