Implementing Custom Window Functions and Running Calculations in Power Query M: Rolling Averages, Cumulative Totals, and Rank-Based Partitioning Without Native Window Support
Introduction
You're building a sales performance report. Your stakeholders want a 7-day rolling average of revenue, a cumulative total that resets at the start of each month, and a rank of each salesperson within their region — all in Power Query, before the data ever reaches the data model. You open the M editor, look for OVER, PARTITION BY, or ROWS BETWEEN — and find nothing. Power Query M has no native window function syntax.
This is one of the most common frustrations practitioners hit when moving from SQL to M. In SQL, you'd write AVG(revenue) OVER (PARTITION BY region ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) and call it a day. In M, you have to build that logic yourself, using list operations, table indexing, and careful partitioning. The good news: once you understand the underlying mechanics, your custom window functions are often more flexible and composable than their SQL equivalents — and they work on any data source, not just ones that support query folding.
By the end of this lesson, you'll be able to implement the most important window calculation patterns in M from scratch. We'll build rolling averages, cumulative sums, partitioned ranks, and lag/lead functions — with reusable helper functions you can drop into any project.
What you'll learn:
- How to simulate SQL-style window functions using list slicing and indexed table access in M
- How to build rolling averages with configurable window sizes over sorted data
- How to compute cumulative totals with group-level resets using partitioned list accumulators
- How to implement dense rank and row number within partitions without native support
- How to package these patterns into reusable, parameterized M functions you can share across queries
Prerequisites
You should be comfortable with core M syntax — let/in expressions, each, record and list operations, and how Table.AddColumn works. If you're fuzzy on any of those, the lesson on M Language Fundamentals: Syntax, Types, and Expressions for Power Query is a solid foundation to revisit first. You should also understand how List.Generate and List.Accumulate work, since we'll use both heavily — the Advanced M: Iterators, Accumulators, and Recursive Patterns lesson covers these in depth.
Understanding the Core Problem: Why M Has No Window Functions
Before we build solutions, it's worth understanding why the gap exists. Window functions in SQL operate at the query engine level — the database can scan a result set multiple times, maintain sorted buffers, and compute running values as part of the execution plan. Power Query M is a functional, lazy-evaluated language designed to express transformations, not to iterate over result sets with stateful cursors.
M processes data in terms of table steps, where each step transforms the entire table. There's no concept of "the previous row" built into the language — a row in M is just a record, and records have no inherent positional awareness. The engine does expose row position through Table.AddIndexColumn, but that's a one-time labeling operation, not a cursor.
The practical implication: to compute any window calculation, you need to:
- Add a position index so rows can reference each other by number
- Define the window as a list slice derived from that index
- Apply an aggregation over that list slice
This pattern is consistent across rolling averages, cumulative sums, and rank calculations. Once you internalize it, the implementations follow naturally.
Key insight: Everything in M's window function toolkit comes down to this: convert the "rows around me" concept into a "sublist of a column's values" concept. Lists in M are the window buffer. List operations are the window aggregation.
Setting Up a Realistic Dataset
Let's use a dataset that will stress-test all our patterns. Imagine daily sales data across three regions, with some missing days (real data never comes in clean). Here's our starting table, which we'll create inline for portability:
let
Source = Table.FromRecords({
[Date = #date(2024,1,1), Region = "North", Revenue = 12400],
[Date = #date(2024,1,2), Region = "North", Revenue = 15300],
[Date = #date(2024,1,3), Region = "North", Revenue = 9800],
[Date = #date(2024,1,4), Region = "North", Revenue = 11200],
[Date = #date(2024,1,5), Region = "North", Revenue = 14500],
[Date = #date(2024,1,6), Region = "North", Revenue = 16100],
[Date = #date(2024,1,7), Region = "North", Revenue = 13700],
[Date = #date(2024,1,8), Region = "North", Revenue = 10900],
[Date = #date(2024,1,9), Region = "North", Revenue = 12600],
[Date = #date(2024,1,10), Region = "North", Revenue = 18200],
[Date = #date(2024,1,1), Region = "South", Revenue = 8400],
[Date = #date(2024,1,2), Region = "South", Revenue = 9100],
[Date = #date(2024,1,3), Region = "South", Revenue = 7600],
[Date = #date(2024,1,4), Region = "South", Revenue = 10200],
[Date = #date(2024,1,5), Region = "South", Revenue = 11400],
[Date = #date(2024,1,6), Region = "South", Revenue = 9800],
[Date = #date(2024,1,7), Region = "South", Revenue = 8900],
[Date = #date(2024,1,8), Region = "South", Revenue = 12300],
[Date = #date(2024,1,9), Region = "South", Revenue = 10500],
[Date = #date(2024,1,10), Region = "South", Revenue = 13700]
}),
TypedTable = Table.TransformColumnTypes(Source, {
{"Date", type date},
{"Region", type text},
{"Revenue", Int64.Type}
})
in
TypedTable
This gives us 20 rows across two regions, ten days each. We'll reference this as TypedTable throughout the lesson. In a real scenario, you'd replace Source with your actual data connector step.
Pattern 1: Rolling Averages with Configurable Window Size
A rolling (or moving) average smooths out short-term volatility to reveal trends. For each row, you want the average of the N rows ending at the current row. In SQL: AVG(Revenue) OVER (ORDER BY Date ROWS BETWEEN N-1 PRECEDING AND CURRENT ROW).
The M approach: add an index, extract the Revenue column as a list, then for each index position, slice the list to get the preceding N values and average them.
Step 1: Sort and Index
Window functions always imply an ordering. You must sort explicitly before indexing — never assume M will preserve source order.
let
Source = TypedTable,
// Sort within each region by date - critical before indexing
Sorted = Table.Sort(Source, {{"Region", Order.Ascending}, {"Date", Order.Ascending}}),
Indexed = Table.AddIndexColumn(Sorted, "RowIndex", 0, 1, Int64.Type)
in
Indexed
Step 2: Extract the Column as a List
This is the key insight. Rather than referencing "other rows" during row-level processing, we extract the entire Revenue column as a list once. Any row can then slice into that list by position.
RevenueList = Table.Column(Indexed, "Revenue"),
Step 3: Add the Rolling Average Column
Now for each row at position i, the window covers positions Max(0, i - windowSize + 1) through i. We use List.Range to extract that slice and List.Average to aggregate it.
WindowSize = 3, // configurable
WithRollingAvg = Table.AddColumn(Indexed, "RollingAvg3Day", each
let
i = [RowIndex],
startPos = Number.Max(0, i - WindowSize + 1),
count = i - startPos + 1,
window = List.Range(RevenueList, startPos, count)
in
List.Average(window),
type number
)
There's a subtle issue here though: this calculates a rolling average across the entire sorted table, but we want rolling averages within each region separately. Row 0 in North should not bleed into row 0 in South. We need partitioned rolling averages.
Warning: If you add an index to a multi-region table and calculate rolling windows without accounting for partition boundaries, you'll silently produce wrong numbers at partition boundaries — the first N rows of a new region will average with the last rows of the previous region. Always validate your window logic against boundary rows.
The Full Partitioned Rolling Average
The correct approach groups by region, computes the rolling average within each group, then combines back. This is more work but produces correct results.
let
Source = TypedTable,
Sorted = Table.Sort(Source, {{"Region", Order.Ascending}, {"Date", Order.Ascending}}),
// Define window size as a variable for easy adjustment
WindowSize = 3,
// Group by region, compute rolling avg within each partition
Grouped = Table.Group(Sorted, {"Region"}, {
{"AllRows", each
let
// Sort within partition (already sorted, but be explicit)
PartitionSorted = Table.Sort(_, {{"Date", Order.Ascending}}),
// Index within partition only
PartitionIndexed = Table.AddIndexColumn(PartitionSorted, "PartIdx", 0, 1, Int64.Type),
// Extract revenue as list for this partition
RevList = Table.Column(PartitionIndexed, "Revenue"),
// Add rolling average column
WithAvg = Table.AddColumn(PartitionIndexed, "RollingAvg", each
let
i = [PartIdx],
startPos = Number.Max(0, i - WindowSize + 1),
count = i - startPos + 1,
window = List.Range(RevList, startPos, count)
in
List.Average(window),
type number
)
in
WithAvg,
type table}
}),
// Expand back to flat table
Expanded = Table.Combine(Grouped[AllRows]),
// Clean up the partition index
Cleaned = Table.RemoveColumns(Expanded, {"PartIdx"})
in
Cleaned
This pattern — group, compute within partition, combine — is the backbone of all partitioned window functions in M. You'll use it for every calculation in this lesson.
Tip: The Table.Group + Table.Combine pattern is your PARTITION BY equivalent in M. Once you internalize this pair, partitioned window functions become much more mechanical to implement.
Pattern 2: Cumulative Totals with Partition-Level Resets
A cumulative sum accumulates values from the first row of a partition through the current row. The tricky version resets at a group boundary — a cumulative monthly total that starts over each month, or a running profit that resets each quarter.
Simple Cumulative Total Within a Partition
Using List.Range again, a cumulative sum at position i is just List.Sum(List.Range(RevList, 0, i + 1)):
let
Source = TypedTable,
Sorted = Table.Sort(Source, {{"Region", Order.Ascending}, {"Date", Order.Ascending}}),
WindowSize = 3, // not used here, but kept for consistency
Grouped = Table.Group(Sorted, {"Region"}, {
{"AllRows", each
let
PartitionSorted = Table.Sort(_, {{"Date", Order.Ascending}}),
PartitionIndexed = Table.AddIndexColumn(PartitionSorted, "PartIdx", 0, 1, Int64.Type),
RevList = Table.Column(PartitionIndexed, "Revenue"),
WithCumSum = Table.AddColumn(PartitionIndexed, "CumulativeRevenue", each
List.Sum(List.Range(RevList, 0, [PartIdx] + 1)),
Int64.Type
)
in
WithCumSum,
type table}
}),
Expanded = Table.Combine(Grouped[AllRows]),
Cleaned = Table.RemoveColumns(Expanded, {"PartIdx"})
in
Cleaned
Cumulative Total with Monthly Reset
Now the harder version: cumulative revenue per region per month. This requires a two-level partition — first by Region, then by month within that region.
let
Source = TypedTable,
// Add a month key for the secondary partition
WithMonth = Table.AddColumn(Source, "YearMonth", each
Date.Year([Date]) * 100 + Date.Month([Date]),
Int64.Type
),
Sorted = Table.Sort(WithMonth, {
{"Region", Order.Ascending},
{"YearMonth", Order.Ascending},
{"Date", Order.Ascending}
}),
// First partition by Region
ByRegion = Table.Group(Sorted, {"Region"}, {
{"RegionRows", each
let
RegionData = _,
// Second partition by YearMonth within Region
ByMonth = Table.Group(RegionData, {"YearMonth"}, {
{"MonthRows", each
let
MonthSorted = Table.Sort(_, {{"Date", Order.Ascending}}),
MonthIndexed = Table.AddIndexColumn(MonthSorted, "MonthIdx", 0, 1, Int64.Type),
RevList = Table.Column(MonthIndexed, "Revenue"),
WithCumSum = Table.AddColumn(MonthIndexed, "CumRevMonth", each
List.Sum(List.Range(RevList, 0, [MonthIdx] + 1)),
Int64.Type
),
Cleaned = Table.RemoveColumns(WithCumSum, {"MonthIdx"})
in
Cleaned,
type table}
}),
Combined = Table.Combine(ByMonth[MonthRows])
in
Combined,
type table}
}),
FinalTable = Table.Combine(ByRegion[RegionRows])
in
FinalTable
This nested grouping pattern directly mirrors PARTITION BY Region, YearMonth ORDER BY Date in SQL. The outer group handles the region partition; the inner group handles the month reset. When you need a third level, you nest one more time.
Note: The List.Range(list, 0, i + 1) approach recalculates the sum by slicing the full list for every row. For large datasets, this means O(n²) operations per partition. For hundreds of rows this is fine; for tens of thousands, consider List.Accumulate to compute running totals in a single pass — we'll cover that optimization shortly.
Pattern 3: Efficient Cumulative Sums with List.Accumulate
The List.Range + List.Sum approach is easy to read but recalculates sums from scratch for every row. List.Accumulate processes a list once and builds up a result, which is dramatically more efficient for large partitions.
// Given a list of numbers, produce a list of cumulative sums
let
ComputeCumulativeSums = (values as list) as list =>
let
// List.Accumulate signature: (list, seed, accumulator)
// accumulator receives (state, current) and returns new state
// We accumulate {runningTotal, resultList}
Result = List.Accumulate(
values,
{0, {}}, // seed: {running total, output list}
(state, current) =>
let
newTotal = state{0} + current,
newList = state{1} & {newTotal}
in
{newTotal, newList}
)
in
Result{1} // return just the result list
in
ComputeCumulativeSums
This function takes a list of revenue values and returns a list of cumulative totals in a single pass. To use it in the partition pattern:
let
Source = TypedTable,
Sorted = Table.Sort(Source, {{"Region", Order.Ascending}, {"Date", Order.Ascending}}),
// The accumulator function
CumSums = (values as list) as list =>
List.Accumulate(values, {0, {}}, (state, current) =>
{state{0} + current, state{1} & {state{0} + current}}
){1},
Grouped = Table.Group(Sorted, {"Region"}, {
{"AllRows", each
let
PartSorted = Table.Sort(_, {{"Date", Order.Ascending}}),
RevList = Table.Column(PartSorted, "Revenue"),
CumSumList = CumSums(RevList),
// Zip the cumulative sums back as a new column
WithCumSum = Table.FromColumns(
Table.ToColumns(PartSorted) & {CumSumList},
Table.ColumnNames(PartSorted) & {"CumulativeRevenue"}
)
in
WithCumSum,
type table}
}),
FinalTable = Table.Combine(Grouped[AllRows])
in
FinalTable
The Table.FromColumns + Table.ToColumns pattern appends a list as a new column without row-by-row processing. This is a powerful technique for bulk-adding computed columns that come from list operations rather than row-level expressions. You can learn more about how table column operations interact with M's evaluation model in the Advanced Table Operations: Group, Join, and Transform in M Language lesson.
Pattern 4: Rank-Based Partitioning (Dense Rank, Row Number, Percent Rank)
Ranking functions are arguably the most requested window functions in real-world reporting. "Rank each salesperson by revenue within their region" is a classic ask. Let's implement dense rank, row number, and percent rank.
Dense Rank Within a Partition
Dense rank assigns rank 1 to the highest value, rank 2 to the next distinct value, and so on — with no gaps when there are ties.
let
Source = TypedTable,
// Aggregate to one row per region per day (already one, but illustrating)
// For ranking, we need total revenue per region
RegionTotals = Table.Group(Source, {"Region"}, {
{"TotalRevenue", each List.Sum([Revenue]), Int64.Type}
}),
// Add dense rank across all regions
// Sort descending by revenue
Sorted = Table.Sort(RegionTotals, {{"TotalRevenue", Order.Descending}}),
// Extract revenue list for rank computation
RevList = Table.Column(Sorted, "TotalRevenue"),
// For each row, count distinct values greater than current
// Dense rank = count of distinct higher values + 1
WithRank = Table.AddColumn(Sorted, "DenseRank", each
let
current = [TotalRevenue],
higherValues = List.Select(RevList, each _ > current),
distinctHigher = List.Count(List.Distinct(higherValues))
in
distinctHigher + 1,
Int64.Type
)
in
WithRank
Partitioned Rank: Each Salesperson Ranked Within Their Region
The more realistic scenario: rank individual salespeople by their revenue, but only compared to others in the same region. This combines the partition pattern with the rank computation:
let
// Simulated salesperson data
SalesData = Table.FromRecords({
[Region = "North", Salesperson = "Alice", Revenue = 145000],
[Region = "North", Salesperson = "Bob", Revenue = 132000],
[Region = "North", Salesperson = "Carol", Revenue = 145000], // tie with Alice
[Region = "North", Salesperson = "Dave", Revenue = 118000],
[Region = "South", Salesperson = "Eve", Revenue = 98000],
[Region = "South", Salesperson = "Frank", Revenue = 112000],
[Region = "South", Salesperson = "Grace", Revenue = 98000], // tie with Eve
[Region = "South", Salesperson = "Henry", Revenue = 125000]
}),
// Partition by Region, rank within each partition
Grouped = Table.Group(SalesData, {"Region"}, {
{"AllRows", each
let
PartData = _,
RevList = Table.Column(PartData, "Revenue"),
// Dense rank: count distinct values higher than current + 1
WithDenseRank = Table.AddColumn(PartData, "DenseRank", each
List.Count(List.Distinct(List.Select(RevList, (v) => v > [Revenue]))) + 1,
Int64.Type
),
// Row number: sort by revenue desc, assign sequential index
SortedForRowNum = Table.Sort(WithDenseRank, {{"Revenue", Order.Descending}}),
WithRowNum = Table.AddIndexColumn(SortedForRowNum, "RowNumber", 1, 1, Int64.Type),
// Percent rank: (rank - 1) / (count - 1)
PartitionSize = Table.RowCount(PartData),
WithPctRank = Table.AddColumn(WithRowNum, "PercentRank", each
if PartitionSize = 1 then 0
else (List.Count(List.Select(RevList, (v) => v > [Revenue])) / (PartitionSize - 1)),
type number
)
in
WithPctRank,
type table}
}),
FinalTable = Table.Combine(Grouped[AllRows])
in
FinalTable
Notice the difference between dense rank and row number here. Dense rank gives Alice and Carol both rank 1 because they have equal revenue. Row number gives them sequential numbers (1 and 2) based on sort order — which of the two gets row number 1 is arbitrary without a tiebreaker column.
Tip: For row number, always define a secondary sort column to break ties deterministically. If you sort only by Revenue and two rows have equal values, the row number assignment is unpredictable and may change between refreshes. Add Salesperson as a secondary sort key: Table.Sort(_, {{"Revenue", Order.Descending}, {"Salesperson", Order.Ascending}}).
Pattern 5: Lag and Lead Functions
Lag and lead let each row reference a value N positions before or after it in the sort order. These are essential for period-over-period comparisons: "how does today's revenue compare to yesterday's?"
Implementing Lag (Previous Row Reference)
let
Source = TypedTable,
Sorted = Table.Sort(Source, {{"Region", Order.Ascending}, {"Date", Order.Ascending}}),
Grouped = Table.Group(Sorted, {"Region"}, {
{"AllRows", each
let
PartSorted = Table.Sort(_, {{"Date", Order.Ascending}}),
PartIndexed = Table.AddIndexColumn(PartSorted, "PartIdx", 0, 1, Int64.Type),
RevList = Table.Column(PartIndexed, "Revenue"),
// Lag by N: value N positions before current row
LagN = 1,
WithLag = Table.AddColumn(PartIndexed, "PrevDayRevenue", each
let
i = [PartIdx],
lagIdx = i - LagN
in
if lagIdx < 0 then null
else RevList{lagIdx},
type nullable number
),
// Add day-over-day change
WithChange = Table.AddColumn(WithLag, "DayOverDayChange", each
if [PrevDayRevenue] = null then null
else [Revenue] - [PrevDayRevenue],
type nullable number
),
WithChangePct = Table.AddColumn(WithChange, "DayOverDayPct", each
if [PrevDayRevenue] = null or [PrevDayRevenue] = 0 then null
else ([Revenue] - [PrevDayRevenue]) / [PrevDayRevenue],
type nullable number
),
Cleaned = Table.RemoveColumns(WithChangePct, {"PartIdx"})
in
Cleaned,
type table}
}),
FinalTable = Table.Combine(Grouped[AllRows])
in
FinalTable
Lead works identically — just change lagIdx = i - LagN to leadIdx = i + LagN and check if leadIdx >= List.Count(RevList) then null.
Packaging Everything as Reusable Functions
At this point you have working implementations, but they're embedded in specific queries. The real productivity gain comes from packaging these patterns as reusable M functions you can call from any query. The Writing Custom M Functions from Scratch in Power Query lesson covers the mechanics of function creation in detail — here we'll apply that knowledge directly.
Create a new blank query named fnWindowFunctions and paste this function library:
let
// =========================================================
// Rolling Average
// Params:
// tbl - source table
// partitionCols - list of column names to partition by
// orderCol - column name to sort by within partition
// valueCol - numeric column to average
// windowSize - number of rows in the rolling window
// outputCol - name for the new column
// =========================================================
RollingAverage = (
tbl as table,
partitionCols as list,
orderCol as text,
valueCol as text,
windowSize as number,
outputCol as text
) as table =>
let
Grouped = Table.Group(tbl, partitionCols, {
{"_Partition", each
let
Sorted = Table.Sort(_, {{orderCol, Order.Ascending}}),
Indexed = Table.AddIndexColumn(Sorted, "_Idx", 0, 1, Int64.Type),
ValList = Table.Column(Indexed, valueCol),
WithAvg = Table.AddColumn(Indexed, outputCol, each
let
i = [_Idx],
startPos = Number.Max(0, i - windowSize + 1),
cnt = i - startPos + 1
in
List.Average(List.Range(ValList, startPos, cnt)),
type number
),
Cleaned = Table.RemoveColumns(WithAvg, {"_Idx"})
in
Cleaned,
type table}
}),
Result = Table.Combine(Grouped[_Partition])
in
Result,
// =========================================================
// Cumulative Sum
// =========================================================
CumulativeSum = (
tbl as table,
partitionCols as list,
orderCol as text,
valueCol as text,
outputCol as text
) as table =>
let
CumSums = (vals as list) as list =>
List.Accumulate(vals, {0, {}}, (state, cur) =>
{state{0} + cur, state{1} & {state{0} + cur}}
){1},
Grouped = Table.Group(tbl, partitionCols, {
{"_Partition", each
let
Sorted = Table.Sort(_, {{orderCol, Order.Ascending}}),
ValList = Table.Column(Sorted, valueCol),
CumList = CumSums(ValList),
Result = Table.FromColumns(
Table.ToColumns(Sorted) & {CumList},
Table.ColumnNames(Sorted) & {outputCol}
)
in
Result,
type table}
}),
FinalTable = Table.Combine(Grouped[_Partition])
in
FinalTable,
// =========================================================
// Dense Rank
// =========================================================
DenseRank = (
tbl as table,
partitionCols as list,
valueCol as text,
ascending as logical,
outputCol as text
) as table =>
let
Grouped = Table.Group(tbl, partitionCols, {
{"_Partition", each
let
ValList = Table.Column(_, valueCol),
WithRank = Table.AddColumn(_, outputCol, each
let
cur = Record.Field(_, valueCol),
higherVals = if ascending
then List.Select(ValList, (v) => v < cur)
else List.Select(ValList, (v) => v > cur)
in
List.Count(List.Distinct(higherVals)) + 1,
Int64.Type
)
in
WithRank,
type table}
}),
FinalTable = Table.Combine(Grouped[_Partition])
in
FinalTable,
// =========================================================
// Lag
// =========================================================
Lag = (
tbl as table,
partitionCols as list,
orderCol as text,
valueCol as text,
lagN as number,
outputCol as text
) as table =>
let
Grouped = Table.Group(tbl, partitionCols, {
{"_Partition", each
let
Sorted = Table.Sort(_, {{orderCol, Order.Ascending}}),
Indexed = Table.AddIndexColumn(Sorted, "_Idx", 0, 1, Int64.Type),
ValList = Table.Column(Indexed, valueCol),
WithLag = Table.AddColumn(Indexed, outputCol, each
let lagIdx = [_Idx] - lagN
in if lagIdx < 0 then null else ValList{lagIdx}
),
Cleaned = Table.RemoveColumns(WithLag, {"_Idx"})
in
Cleaned,
type table}
}),
FinalTable = Table.Combine(Grouped[_Partition])
in
FinalTable,
// Expose as a record so callers can access individual functions
Functions = [
RollingAverage = RollingAverage,
CumulativeSum = CumulativeSum,
DenseRank = DenseRank,
Lag = Lag
]
in
Functions
Now you can call these functions from any query in the same Power BI file or Excel workbook:
let
Source = TypedTable,
Win = fnWindowFunctions,
// Apply rolling 3-day average within each region, ordered by date
Step1 = Win[RollingAverage](Source, {"Region"}, "Date", "Revenue", 3, "RollingAvg3"),
// Add cumulative sum within each region
Step2 = Win[CumulativeSum](Step1, {"Region"}, "Date", "Revenue", "CumRevenue"),
// Add day-over-day lag
Step3 = Win[Lag](Step2, {"Region"}, "Date", "Revenue", 1, "PrevRevenue")
in
Step3
This chaining approach is clean, readable, and gives you a mini window-function DSL on top of M. For strategies on organizing these shared functions across multiple reports, the Cross-Query State Management and Shared Parameter Tables in Power Query M article covers centralized configuration patterns that apply here.
Key insight: Storing your function library in a single query that returns a record of functions lets you version and update the library in one place. Every query that calls fnWindowFunctions will automatically pick up changes when you update the library query — no hunting through individual transformation steps.
This is the honest section. Custom window functions in M are powerful, but they come with real trade-offs.
Query Folding Is Gone
Any window calculation you implement in M will execute in the Power Query engine, not the data source. This means you lose query folding for downstream steps. If you're working with SQL Server or another foldable source, always filter and aggregate as much as possible before adding window function steps. The Implementing Custom Query Folding Logic in M article explains how to structure queries to preserve folding as long as possible.
Computational Complexity
| Pattern |
Complexity per partition |
Notes |
| Rolling avg (List.Range + List.Sum) |
O(n × w) |
w = window size |
| Cumulative sum (List.Accumulate) |
O(n) |
Single pass |
| Dense rank (List.Select per row) |
O(n²) |
Can be optimized with pre-sort |
| Lag/Lead |
O(n) |
Direct index access |
For partitions with fewer than ~5,000 rows, all of these are fast enough for interactive Power Query. For larger partitions, prefer List.Accumulate-based approaches and avoid nested List.Select in the hot path.
Alternatives to Consider
If your data source supports SQL window functions, consider computing them there and loading pre-computed columns into Power Query. Power Query is the right place for these calculations when: your source doesn't support window functions, you're working with file sources (CSV, Excel, JSON), or you need the calculations to be portable across different data sources in the same report.
Warning: The Table.Group + Table.Combine pattern creates intermediate in-memory tables for every partition. For very wide tables (many columns), this amplifies memory pressure because M can't lazily discard columns during grouping. If you hit memory issues, select only the columns you need before the grouping step: Table.SelectColumns(Source, {"Region", "Date", "Revenue"}) before Table.Group.
Hands-On Exercise
Build a complete Month-over-Month sales analysis query using the techniques from this lesson. Your query should:
- Start with the
TypedTable defined at the top of this lesson
- Add a
YearMonth column formatted as YYYYMM integer
- Compute total revenue per region per month using
Table.Group
- Within each region, add:
- A cumulative monthly revenue column (running total of monthly aggregates, ordered by
YearMonth)
- A previous month revenue column using the lag pattern (lag of 1 on the monthly total)
- A month-over-month growth % column:
(CurrentMonth - PrevMonth) / PrevMonth
- A dense rank of each month by revenue within the region (rank 1 = highest revenue month)
- Sort the final output by Region ascending, then YearMonth ascending
For the dataset provided, you'll only have one month of data — extend the sample data with February entries to make the lag and MoM calculations meaningful. Use the fnWindowFunctions library for at least two of the four window calculations.
Bonus challenge: Parameterize the growth threshold so that months with MoM growth above a configurable percentage get flagged with a "Strong Growth" label, while months below get "Needs Attention". Store the threshold as a parameter query and reference it in your calculation. The Dynamic Queries with M: Build Flexible, Reusable Transformations article covers parameter-driven query patterns.
Common Mistakes & Troubleshooting
Mistake 1: Forgetting to Sort Before Indexing
Symptom: Rolling averages look wrong; values don't correspond to the expected date sequence.
Fix: Always include an explicit Table.Sort before Table.AddIndexColumn. Power Query does not guarantee row order between steps, especially after joins or group operations.
Mistake 2: Window Bleeding Across Partition Boundaries
Symptom: The first row of region "South" shows a rolling average that includes values from region "North."
Fix: Use the Table.Group + partition-level indexing pattern. Never add a global index and use it for partitioned windows.
Mistake 3: Null Handling in Window Calculations
Symptom: List.Average or List.Sum returns null or unexpected results when the source column has null values.
Fix: Use List.Select to filter nulls before aggregating: List.Average(List.Select(window, each _ <> null)). Decide explicitly whether nulls should be treated as zero or excluded.
Mistake 4: Type Errors After Table.Combine
Symptom: After combining partitions, column types are inferred as any instead of the expected numeric type.
Fix: Add explicit type specifications to Table.AddColumn calls: the third argument is the type, e.g. Int64.Type or type number. Alternatively, add a Table.TransformColumnTypes step after the final Table.Combine. Understanding M's type coercion behavior is covered in depth in the M Language Data Types and Type Coercion in Power Query lesson.
Mistake 5: Using Record.Field Instead of Direct Field Access in Lambda
Symptom: Inside Table.AddColumn, accessing [ColumnName] works but Record.Field(_, "ColumnName") behaves unexpectedly when the column name contains spaces or special characters.
Fix: For columns with spaces, use Record.Field(_, "Column Name") explicitly. For programmatic column name access (where the name is a variable), Record.Field is required — direct bracket syntax only works with literal column names.
Debugging Window Calculations
When results look wrong, isolate a single partition for inspection. Add a filter step immediately after your Table.Group to examine just one group:
// Debug: examine just the North partition
DebugPartition = Table.SelectRows(Source, each [Region] = "North"),
// Apply your window function only to this partition
DebugResult = Win[RollingAverage](DebugPartition, {"Region"}, "Date", "Revenue", 3, "RollingAvg3")
This makes it easy to verify that the logic is correct before scaling to all partitions. Performance tuning approaches for complex M queries are covered in M Language Performance Patterns and Anti-Patterns.
Summary & Next Steps
You now have a complete toolkit for implementing SQL-style window functions in Power Query M:
- Rolling averages using sorted indexes and
List.Range slicing, partitioned with Table.Group
- Cumulative sums with single-pass
List.Accumulate for efficiency, supporting arbitrary partition resets
- Dense rank and row number within partitions using comparison-based counting
- Lag and lead functions for period-over-period comparisons using direct index access
- A reusable function library pattern that packages all of these into callable, parameterized functions
The unifying principle across all of these: M doesn't have row-level awareness, so you create it. You extract column values as lists, slice those lists by position, and aggregate the slices. The Table.Group + Table.Combine pattern is your PARTITION BY. The index column is your cursor position. List operations are your window aggregation.
Where to go next:
