Here's a scenario that should feel familiar: You've got a sales dataset with thousands of rows, and your manager wants a report showing the weighted average selling price by product category — but weighted by units sold, not a simple mean. Or maybe you need the 90th percentile order value by region. Or conditional aggregations where the denominator changes based on a flag column. You open Power Query, reach for the Group By button, and... hit a wall. Native Group By gives you Sum, Average, Min, Max, Count, and a handful of other operations. That's it.
The instinct at this point is usually to surrender the aggregation to DAX or write a SQL query upstream. Sometimes that's the right call. But often, you're dealing with data that doesn't yet live in a model, or you need the transformation to be self-contained in the query layer, or your data source doesn't support SQL. That's where building a custom aggregation engine in M pays dividends. The M language is far more capable than the Power Query UI suggests — it's a functional, lazily-evaluated language with first-class functions, recursive data structures, and enough flexibility to build sophisticated statistical machinery from scratch.
By the end of this lesson, you'll understand the mechanics of M's aggregation model deeply enough to implement anything the native UI can't give you. You'll build weighted averages, percentile calculations, conditional aggregations, rolling windows, and composite multi-statistic summaries — all within Power Query's transformation layer.
What you'll learn:
List functions and Table.Group actually work under the hood, and why that matters for custom aggregationThis lesson assumes you're comfortable with Power Query M beyond the basics. Specifically, you should understand:
(param) => expression syntaxList, Table, and Record typesTable.Group works at a surface levellet...in expressions and step chainingIf you can write a custom Group By aggregation that uses each List.Sum([Column]), you're ready for this lesson.
Before we build anything custom, we need to understand what Table.Group is actually doing — because the moment you understand its internals, the path to custom aggregations becomes obvious.
When you call Table.Group, M partitions your table into subtables based on the key columns you specify. Each subtable is a full Table value containing all rows that share those key column values. The aggregation step then runs a function against each subtable to produce a scalar value (or another structured value) for each group.
Here's what a native Group By looks like in M:
Table.Group(
SalesData,
{"Region", "Category"},
{
{"Total Revenue", each List.Sum([Revenue]), type number},
{"Order Count", each Table.RowCount(_), type number}
}
)
Notice that each List.Sum([Revenue]) is shorthand for (_) => List.Sum(_[Revenue]). The underscore _ is the subtable — the full partition of rows that share the same Region and Category values. This is the key insight: you have access to the entire subtable, not just a column.
That means you can compute anything that's computable from a table. The subtable has all the original columns, all the rows, and you can apply any M expression to it. The UI's Group By dialog just presents a simplified interface over this much more powerful primitive.
Let's prove this to ourselves with an example. Suppose you have this dataset:
Region | Category | Revenue | Units | IsPromo
----------|-------------|----------|-------|--------
North | Electronics | 5000 | 10 | true
North | Electronics | 3000 | 8 | false
North | Electronics | 7000 | 15 | true
South | Apparel | 1200 | 6 | false
South | Apparel | 900 | 4 | true
A native Group By on Region and Category would give you simple aggregations. But the subtable for {North, Electronics} contains all three rows with all five columns — Revenue, Units, IsPromo, everything. That's your raw material.
The weighted average is the first aggregation that breaks native Group By. The formula is straightforward: sum of (value × weight) divided by sum of weights. But expressing that across a subtable requires you to work with two columns simultaneously.
let
Source = SalesData,
WeightedAvgPrice = Table.Group(
Source,
{"Region", "Category"},
{
{
"Weighted Avg Price",
each
let
revenues = [Revenue],
units = [Units],
numerator = List.Sum(
List.Transform(
List.Positions(revenues),
(i) => revenues{i} * units{i}
)
),
denominator = List.Sum(units)
in
if denominator = 0 then null else numerator / denominator,
type number
}
}
)
in
WeightedAvgPrice
This works but it's a bit verbose. The List.Positions call generates an index list {0, 1, 2, ...} which we then use to zip the two lists together by position. This is M's functional way of doing what most languages handle with a loop.
There's a cleaner pattern using List.Accumulate or by converting to a table first:
{
"Weighted Avg Price",
each
let
pairs = Table.ToRows(Table.SelectColumns(_, {"Revenue", "Units"})),
numerator = List.Sum(List.Transform(pairs, (row) => row{0} * row{1})),
denominator = List.Sum([Units])
in
if denominator = 0 then null else numerator / denominator,
type number
}
Performance note:
Table.ToRowscreates a list of lists, which is slightly more memory-intensive than working with column lists directly. For large subtables (tens of thousands of rows per group), the index-basedList.Positionspattern tends to perform better because it avoids materializing an intermediate table structure.
Rather than repeating this logic every time, let's extract it into a named function you can call across any Group By:
let
WeightedAverage = (subtable as table, valueCol as text, weightCol as text) as nullable number =>
let
values = Table.Column(subtable, valueCol),
weights = Table.Column(subtable, weightCol),
positions = List.Positions(values),
numerator = List.Sum(
List.Transform(positions, (i) => values{i} * weights{i})
),
denominator = List.Sum(weights)
in
if denominator = 0 then null else numerator / denominator
in
WeightedAverage
Now your Group By becomes clean and readable:
Table.Group(
Source,
{"Region", "Category"},
{
{"Weighted Avg Price", each WeightedAverage(_, "Revenue", "Units"), type number},
{"Weighted Avg Discount", each WeightedAverage(_, "Discount", "Units"), type number}
}
)
This is the pattern that scales. Define your aggregation logic once as a parameterized function, then reference it cleanly in the Group By spec.
Weighted standard deviation is the next level. The formula for population weighted standard deviation is:
σ_w = sqrt( Σ(w_i × (x_i - μ_w)²) / Σ(w_i) )
where μ_w is the weighted mean. In M:
let
WeightedStdDev = (subtable as table, valueCol as text, weightCol as text) as nullable number =>
let
values = Table.Column(subtable, valueCol),
weights = Table.Column(subtable, weightCol),
positions = List.Positions(values),
totalWeight = List.Sum(weights),
weightedMean =
if totalWeight = 0 then null
else List.Sum(
List.Transform(positions, (i) => values{i} * weights{i})
) / totalWeight,
weightedVariance =
if weightedMean = null then null
else List.Sum(
List.Transform(
positions,
(i) => weights{i} * Number.Power(values{i} - weightedMean, 2)
)
) / totalWeight
in
if weightedVariance = null then null else Number.Sqrt(weightedVariance)
in
WeightedStdDev
Notice how the intermediate weightedMean feeds into weightedVariance. This multi-step computation inside a let block within the aggregation function is the core pattern for any statistic that requires multiple passes over the data.
Warning: M does not short-circuit null propagation the same way SQL does. If any value in your list is
null,List.Sumwill returnnullfor the whole sum. UseList.RemoveNullsorList.ReplaceValueto handle nulls explicitly before computing.
Percentile calculations are where things get genuinely interesting. There's no List.Percentile in M (as of this writing), so you have to implement the ranking logic yourself. This is actually a great exercise because it forces you to understand the interpolation behavior that different percentile methods use.
The most common approach (used by Excel's PERCENTILE.INC and Python's numpy default) is linear interpolation:
rank = p × (n - 1) where p is the percentile (0 to 1) and n is the countk and fractional part fresult = sorted[k] + f × (sorted[k+1] - sorted[k])let
Percentile = (values as list, p as number) as nullable number =>
let
cleaned = List.Sort(List.RemoveNulls(values)),
n = List.Count(cleaned),
result =
if n = 0 then null
else if n = 1 then cleaned{0}
else
let
rank = p * (n - 1),
k = Number.RoundDown(rank),
f = rank - k,
lower = cleaned{k},
upper = if k + 1 < n then cleaned{k + 1} else cleaned{k}
in
lower + f * (upper - lower)
in
result
in
Percentile
Now you can use this inside a Group By to compute, say, the 50th, 90th, and 95th percentiles simultaneously:
Table.Group(
Source,
{"Region", "Category"},
{
{"Median Revenue", each Percentile([Revenue], 0.5), type number},
{"P90 Revenue", each Percentile([Revenue], 0.9), type number},
{"P95 Revenue", each Percentile([Revenue], 0.95), type number},
{"Count", each Table.RowCount(_), type number}
}
)
Here's an important subtlety: when you list three percentile columns like above, M runs the aggregation function three times for each group, sorting the list each time. For large groups, this matters.
The solution is to compute all percentiles in a single pass per group by returning a record from a single aggregation:
Table.Group(
Source,
{"Region", "Category"},
{
{
"Revenue Stats",
each
let
vals = List.RemoveNulls([Revenue]),
sorted = List.Sort(vals)
in
[
Median = Percentile(sorted, 0.5),
P90 = Percentile(sorted, 0.9),
P95 = Percentile(sorted, 0.95),
Count = List.Count(vals)
],
type record
}
}
)
This produces a column of records, which you then expand. But more importantly, the sort happens only once per group. When dealing with groups of 10,000+ rows, this can cut your refresh time significantly.
After getting the record column, expand it:
Table.ExpandRecordColumn(
GroupedTable,
"Revenue Stats",
{"Median", "P90", "P95", "Count"},
{"Median Revenue", "P90 Revenue", "P95 Revenue", "Revenue Count"}
)
Tip: The record-per-group pattern is one of the most underused optimization techniques in Power Query. Any time you're doing multiple aggregations over the same column in the same Group By, consider consolidating them into a single record-returning aggregation to avoid redundant computation.
Conditional aggregations are SQL's SUM(CASE WHEN condition THEN value END) pattern. In M, this is implemented by filtering the subtable before aggregating.
Let's say you want, per region and category:
Table.Group(
Source,
{"Region", "Category"},
{
{
"Promo Revenue",
each List.Sum(
Table.SelectRows(_, each [IsPromo] = true)[Revenue]
),
type number
},
{
"Non-Promo Revenue",
each List.Sum(
Table.SelectRows(_, each [IsPromo] = false)[Revenue]
),
type number
},
{
"Promo Revenue Ratio",
each
let
total = List.Sum([Revenue]),
promo = List.Sum(
Table.SelectRows(_, each [IsPromo] = true)[Revenue]
)
in
if total = 0 or total = null then null else promo / total,
type number
}
}
)
The Table.SelectRows(_, each [IsPromo] = true) call filters the subtable to rows where IsPromo is true, then [Revenue] extracts that column as a list for summing.
What if the conditions aren't hardcoded — what if they come from a parameter table? This is where M's function-as-first-class-value capability becomes powerful.
Suppose you have a ConditionTable that looks like:
MetricName | FilterColumn | FilterValue | AggColumn | AggFunction
------------------|--------------|-------------|-----------|------------
PromoRevenue | IsPromo | true | Revenue | Sum
HighValueRevenue | Tier | High | Revenue | Sum
MidValueCount | Tier | Mid | Units | Count
You can build a function that reads this table and generates the aggregation spec dynamically:
let
BuildConditionalAggregations = (conditionTable as table) as list =>
List.Transform(
Table.ToRows(conditionTable),
(row) =>
let
metricName = row{0},
filterCol = row{1},
filterVal = row{2},
aggCol = row{3},
aggFunc = row{4}
in
{
metricName,
(subtable) =>
let
filtered = Table.SelectRows(
subtable,
(r) => Record.Field(r, filterCol) = filterVal
),
colValues = Table.Column(filtered, aggCol)
in
if aggFunc = "Sum" then List.Sum(colValues)
else if aggFunc = "Count" then List.Count(colValues)
else if aggFunc = "Average" then List.Average(colValues)
else null,
type number
}
)
in
BuildConditionalAggregations
Then use it:
let
aggSpec = BuildConditionalAggregations(ConditionTable),
result = Table.Group(Source, {"Region", "Category"}, aggSpec)
in
result
This is a genuinely powerful pattern: you've externalized the aggregation specification into a config table. Adding new conditional metrics doesn't require touching the query logic — just update the config table.
Warning: When using dynamic functions built in closures like this, be careful about variable capture. The
filterCol,filterVal, andaggFuncvalues must be properly bound within each iteration of the outerList.Transform. M handles this correctly through lexical scoping, but if you're debugging unexpected results, add explicitletbindings inside the lambda to ensure values are captured by value, not by reference.
Rolling windows (moving averages, cumulative sums) require a fundamentally different approach because they're inherently positional — each row's output depends on neighboring rows, not just its group.
M doesn't have native window functions, but you can simulate them by working at the table level. The key idea: add an index column, then for each row, compute an aggregation over a slice of the table.
let
Source = SalesData,
// Add an index to preserve row order
Indexed = Table.AddIndexColumn(Source, "RowIndex", 0, 1),
WindowSize = 7,
// For each row, compute average of preceding N rows (including current)
WithMovingAvg = Table.AddColumn(
Indexed,
"7-Day Moving Avg Revenue",
(row) =>
let
currentIndex = row[RowIndex],
windowStart = Number.Max(0, currentIndex - WindowSize + 1),
windowRows = Table.SelectRows(
Indexed,
each [RowIndex] >= windowStart and [RowIndex] <= currentIndex
),
revenues = Table.Column(windowRows, "Revenue")
in
List.Average(revenues),
type number
)
in
WithMovingAvg
Critical performance warning: This pattern is O(n²) — for each row, it scans a portion of the entire table. For datasets with more than a few thousand rows, this will be extremely slow. For production use with large datasets, you should either push this computation to the data source (SQL window functions), or implement it using
List.Accumulateto maintain a running state:
List.Accumulate is M's fold operation, and it's the right tool for truly sequential computations where each step depends on the previous state.
let
Source = SalesData,
revenues = Source[Revenue],
WindowSize = 7,
n = List.Count(revenues),
// Accumulate: state = {currentIndex, list of moving averages so far}
accumulated = List.Accumulate(
List.Positions(revenues),
{0, {}}, // initial state: {index, results}
(state, i) =>
let
windowStart = Number.Max(0, i - WindowSize + 1),
windowVals = List.Range(revenues, windowStart, i - windowStart + 1),
movingAvg = List.Average(windowVals),
newResults = state{1} & {movingAvg}
in
{i + 1, newResults}
),
movingAvgList = accumulated{1},
// Add back to table as a column
ResultTable = Table.FromColumns(
Table.ToColumns(Source) & {movingAvgList},
Table.ColumnNames(Source) & {"7-Day Moving Avg Revenue"}
)
in
ResultTable
This is still O(n×w) where w is the window size, but it avoids the full table scan on each row. The List.Range(revenues, windowStart, length) call extracts a slice of the list directly.
Now let's bring everything together into an architecture that can handle multiple aggregation types — statistical, weighted, conditional, and simple — through a unified interface.
The goal: a reusable query function that accepts a table, a list of group-by columns, and an aggregation specification record, and returns a fully aggregated result with any combination of aggregation types.
We'll define an aggregation spec as a list of records, where each record describes one output column:
let
// Aggregation spec: list of records
AggSpec = {
// Simple aggregations
[Name = "Total Revenue", Type = "Sum", Column = "Revenue"],
[Name = "Order Count", Type = "Count", Column = "Revenue"],
[Name = "Avg Revenue", Type = "Average", Column = "Revenue"],
[Name = "Min Revenue", Type = "Min", Column = "Revenue"],
[Name = "Max Revenue", Type = "Max", Column = "Revenue"],
// Statistical aggregations
[Name = "Median Revenue", Type = "Percentile", Column = "Revenue", Param = 0.5],
[Name = "P90 Revenue", Type = "Percentile", Column = "Revenue", Param = 0.9],
[Name = "StdDev Revenue", Type = "StdDev", Column = "Revenue"],
// Weighted aggregations
[Name = "Weighted Avg Price", Type = "WeightedAvg", Column = "Revenue", WeightCol = "Units"],
// Conditional aggregations
[Name = "Promo Revenue", Type = "ConditionalSum", Column = "Revenue",
FilterCol = "IsPromo", FilterVal = true],
[Name = "Non-Promo Revenue", Type = "ConditionalSum", Column = "Revenue",
FilterCol = "IsPromo", FilterVal = false]
}
in
AggSpec
Now we build the engine that interprets this spec:
let
AggregationEngine = (
sourceTable as table,
groupByColumns as list,
aggSpec as list
) as table =>
let
// Helper: safely get a record field with a default
GetField = (rec as record, field as text, default as any) =>
if Record.HasFields(rec, {field}) then Record.Field(rec, field) else default,
// Helper: percentile function
PercentileFn = (values as list, p as number) as nullable number =>
let
cleaned = List.Sort(List.RemoveNulls(values)),
n = List.Count(cleaned)
in
if n = 0 then null
else if n = 1 then cleaned{0}
else
let
rank = p * (n - 1),
k = Number.RoundDown(rank),
f = rank - k,
lower = cleaned{k},
upper = if k + 1 < n then cleaned{k + 1} else cleaned{k}
in
lower + f * (upper - lower),
// Helper: standard deviation
StdDevFn = (values as list) as nullable number =>
let
cleaned = List.RemoveNulls(values),
n = List.Count(cleaned)
in
if n < 2 then null
else
let
mean = List.Average(cleaned),
sumSquaredDiff = List.Sum(
List.Transform(cleaned, (v) => Number.Power(v - mean, 2))
)
in
Number.Sqrt(sumSquaredDiff / (n - 1)), // sample std dev
// Helper: weighted average
WeightedAvgFn = (subtable as table, valueCol as text, weightCol as text) as nullable number =>
let
vals = Table.Column(subtable, valueCol),
weights = Table.Column(subtable, weightCol),
positions = List.Positions(vals),
denom = List.Sum(weights)
in
if denom = 0 or denom = null then null
else List.Sum(
List.Transform(positions, (i) => vals{i} * weights{i})
) / denom,
// Build the Table.Group aggregation list from the spec
BuildAggList = (spec as list) as list =>
List.Transform(
spec,
(aggDef) =>
let
name = aggDef[Name],
aggType = aggDef[Type],
col = GetField(aggDef, "Column", null),
param = GetField(aggDef, "Param", null),
weightCol = GetField(aggDef, "WeightCol", null),
filterCol = GetField(aggDef, "FilterCol", null),
filterVal = GetField(aggDef, "FilterVal", null),
aggFn =
if aggType = "Sum" then
(t) => List.Sum(Table.Column(t, col))
else if aggType = "Count" then
(t) => List.Count(List.RemoveNulls(Table.Column(t, col)))
else if aggType = "Average" then
(t) => List.Average(Table.Column(t, col))
else if aggType = "Min" then
(t) => List.Min(Table.Column(t, col))
else if aggType = "Max" then
(t) => List.Max(Table.Column(t, col))
else if aggType = "Percentile" then
(t) => PercentileFn(Table.Column(t, col), param)
else if aggType = "StdDev" then
(t) => StdDevFn(Table.Column(t, col))
else if aggType = "WeightedAvg" then
(t) => WeightedAvgFn(t, col, weightCol)
else if aggType = "ConditionalSum" then
(t) =>
let
filtered = Table.SelectRows(
t,
(r) => Record.Field(r, filterCol) = filterVal
)
in
List.Sum(Table.Column(filtered, col))
else
(t) => null
in
{name, aggFn, type number}
),
aggList = BuildAggList(aggSpec),
result = Table.Group(sourceTable, groupByColumns, aggList)
in
result
in
AggregationEngine
let
Source = SalesData,
GroupCols = {"Region", "Category"},
Spec = {
[Name = "Total Revenue", Type = "Sum", Column = "Revenue"],
[Name = "Order Count", Type = "Count", Column = "OrderID"],
[Name = "Median Revenue", Type = "Percentile", Column = "Revenue", Param = 0.5],
[Name = "P90 Revenue", Type = "Percentile", Column = "Revenue", Param = 0.9],
[Name = "Weighted Avg Price", Type = "WeightedAvg", Column = "Revenue", WeightCol = "Units"],
[Name = "Revenue StdDev", Type = "StdDev", Column = "Revenue"],
[Name = "Promo Revenue", Type = "ConditionalSum", Column = "Revenue",
FilterCol = "IsPromo", FilterVal = true]
},
Result = AggregationEngine(Source, GroupCols, Spec)
in
Result
This engine is the kind of thing you build once and reuse forever. Adding a new aggregation type means adding one branch to the if chain. Adding a new metric means adding one record to the spec list.
You have a dataset called OrderData with the following columns:
OrderDate (date)SalespersonID (text)ProductLine (text)Revenue (number)CostOfGoods (number)Units (number)IsReturned (logical)CustomerTier ("Platinum", "Gold", "Silver")Your task is to produce an aggregation by SalespersonID and ProductLine that includes all of the following in a single Table.Group call:
Part 1: Standard metrics
Part 2: Statistical metrics
Part 3: Weighted metrics
Part 4: Conditional metrics
Challenge: For the multi-condition filter (Part 4, last item), you cannot use FilterCol and FilterVal parameters from the engine above because you need two conditions. Extend the AggregationEngine to support a FilterFn parameter — a row-level function that the engine applies to the subtable. Demonstrate how you'd pass that in via the spec record.
Expected approach:
Add a new aggType branch called "ConditionalSumFn" that uses GetField(aggDef, "FilterFn", null) as the filter, then add this to your spec:
[
Name = "Non-Return Platinum Revenue",
Type = "ConditionalSumFn",
Column = "Revenue",
FilterFn = (r) => r[IsReturned] = false and r[CustomerTier] = "Platinum"
]
Inside Table.Group, each [ColumnName] works when the implicit context is the subtable. But when you're inside a nested function — like inside List.Transform or List.Accumulate — the each keyword binds to the innermost iterable, not the subtable. This causes confusing errors.
Wrong:
each List.Transform([Revenue], each _ * 2) // second 'each' binds to Revenue list items
Right:
(subtable) => List.Transform(subtable[Revenue], (v) => v * 2)
When in doubt, use explicit named parameters instead of each.
List.Sum({1, 2, null, 4}) returns null, not 7. This surprises people who come from SQL where SUM ignores nulls. Always use List.RemoveNulls before aggregating unless you explicitly want null propagation.
// Null-safe sum
List.Sum(List.RemoveNulls([Revenue]))
If you have three aggregation columns that all sort the same Revenue list, M will sort that list three times per group. This is not obvious from the code. Use the record-per-group consolidation pattern when you're computing multiple statistics from the same column.
When you filter a subtable and it returns zero rows, subsequent operations need to handle empty tables gracefully.
// Dangerous: will return null if no rows match
List.Sum(Table.SelectRows(_, each [IsPromo] = true)[Revenue])
// Safe: List.Sum of an empty list returns 0, but you may want null
// Use this if you want null instead of 0 for empty groups:
let
filtered = Table.SelectRows(_, each [IsPromo] = true),
vals = filtered[Revenue]
in
if List.IsEmpty(vals) then null else List.Sum(vals)
When building functions dynamically inside List.Transform, variables from the outer scope can be captured by reference in unexpected ways. Always test dynamic aggregation engines with varied spec entries to verify each aggregation is using the right parameters.
// Potentially problematic — col might not be captured correctly in all scenarios
List.Transform(specs, (s) =>
let col = s[Column]
in (t) => List.Sum(t[col]) // This is fine because col is bound in this let block
)
If your custom aggregation can return null (which most should, to handle empty groups), the type annotation should be type nullable number, not type number. Mismatches here can cause type errors downstream when the column is used in calculated columns.
{"Median Revenue", each Percentile([Revenue], 0.5), type nullable number}
Power Query runs M in-process when evaluating against imported data, but the performance characteristics are important to understand:
Folding: If your data source supports query folding (SQL Server, Dataverse, etc.), Table.Group with native aggregation types will fold to the source. Custom M functions break query folding. This means large datasets that could be aggregated at the source will be fully imported and aggregated in memory. Always check the query plan (right-click a step and check "View Native Query") when working with foldable sources.
Memory: Each subtable in a Group By is materialized in memory simultaneously during evaluation. For datasets with many small groups, this is fine. For datasets where groups are very large (hundreds of thousands of rows per group), memory pressure can be significant.
Parallelism: Power Query does evaluate some operations in parallel, but the degree varies by context (Power BI Desktop vs. Power BI Service vs. Excel). Don't assume sequential execution order within independent aggregation columns.
Optimization strategy: For large datasets requiring complex custom aggregations, consider a hybrid approach: fold simple group-by operations to the source to reduce data volume, then apply complex M aggregations to the reduced result set.
You've now built a complete understanding of how to move beyond Power Query's native Group By into fully custom aggregation territory. The core principles:
Table.Group gives you the full subtable — everything you'd ever need to compute any aggregate statisticTable.SelectRows before List.SumWhat to explore next:
List.Accumulate for stateful computations — it unlocks running totals, exponential smoothing, and other sequential algorithmsThe aggregation engine pattern you've built here is the foundation for building self-service analytics infrastructure in Power Query — where business analysts can add new metrics through configuration rather than code. That's the level where mastery of M starts generating serious organizational leverage.
Learning Path: Advanced M Language