Advanced M: Iterators, Accumulators, and Recursive Patterns

Imagine you're working with a messy sales dataset where each row represents a transaction, but some customers appear across multiple rows that need to be aggregated in a specific way. Or perhaps you need to calculate running totals that depend on complex business rules that can't be handled by simple window functions. These scenarios push beyond what basic M transformations can handle—they require you to think iteratively and build solutions that process data one piece at a time.

Traditional data transformations often assume you can operate on entire columns or tables at once. But real-world data problems frequently require you to examine each record in context, build up results incrementally, or apply logic that depends on what you've seen so far. This is where M's functional programming capabilities shine through iterators, accumulators, and recursive patterns.

By the end of this lesson, you'll have the tools to tackle complex data transformations that seemed impossible before. You'll understand how to process data iteratively, maintain state across operations, and build elegant solutions to problems that would otherwise require multiple complex steps.

What you'll learn:

• How to use List.Generate to create custom iterative processes
• The accumulator pattern for building results incrementally
• List.Accumulate for stateful transformations across datasets
• Recursive functions for hierarchical and self-referencing data
• When to choose iterative vs. recursive approaches
• Performance considerations for advanced M patterns

Prerequisites

You should be comfortable with basic M functions, understand how lists and records work in Power Query, and have experience writing custom functions. Familiarity with basic functional programming concepts like higher-order functions will be helpful but not required.

Understanding the Iterator Mindset

Before diving into specific functions, let's understand what iterative thinking means in M. Traditional spreadsheet thinking operates on entire columns: "sum this range," "filter these rows," "join these tables." Iterative thinking processes data one element at a time: "for each row, look at what I've seen so far and decide what to do next."

Consider a simple example: calculating a running balance for bank transactions. In spreadsheet thinking, you might create a helper column with formulas. In M's iterative approach, you process each transaction sequentially, maintaining the current balance as state.

let
    transactions = {100, -50, 200, -75, 150},
    
    // Traditional approach might use List.Sum with List.Range
    // But what if we need complex running calculations?
    
    runningBalance = List.Accumulate(
        transactions,
        0,  // starting balance
        (state, current) => state + current
    )
in
    runningBalance

This returns {100, 50, 250, 175, 325} - each element representing the running balance after that transaction. The key insight is that we're building our result by processing each element while carrying forward state (the running balance).

List.Generate: The Foundation of Iteration

List.Generate is M's most powerful iterative function. It creates lists by repeatedly applying a function until a condition is met. Think of it as a sophisticated loop that builds a list element by element.

The syntax is:

List.Generate(initial, condition, next, optional selector)

Let's break this down with a practical example. Suppose you're analyzing loan payments and need to generate an amortization schedule:

let
    principal = 100000,
    monthlyRate = 0.005,  // 6% annual = 0.5% monthly
    months = 360,         // 30 years
    
    monthlyPayment = 599.55,  // calculated separately
    
    schedule = List.Generate(
        [Month = 0, Balance = principal, Payment = 0, Interest = 0, Principal = 0],
        (state) => state[Month] < months,
        (state) => 
            let
                newMonth = state[Month] + 1,
                interestPayment = state[Balance] * monthlyRate,
                principalPayment = monthlyPayment - interestPayment,
                newBalance = state[Balance] - principalPayment
            in
                [
                    Month = newMonth,
                    Balance = newBalance,
                    Payment = monthlyPayment,
                    Interest = interestPayment,
                    Principal = principalPayment
                ]
    )
in
    schedule

This generates a complete amortization schedule. Each iteration:

1. Checks if we've reached the final month (condition)
2. Calculates interest based on remaining balance
3. Determines principal payment
4. Updates balance for next iteration

The beauty of List.Generate is how it handles complex state. Each iteration receives the complete state from the previous iteration, allowing for sophisticated calculations that depend on history.

The Accumulator Pattern

While List.Generate creates new lists, List.Accumulate transforms existing data while maintaining state. This is the accumulator pattern - you "accumulate" results by processing each element and carrying forward information.

Let's solve a real-world problem: calculating customer lifetime value where each purchase affects the discount rate for future purchases.

let
    customerPurchases = {
        [Date = #date(2023,1,15), Amount = 100],
        [Date = #date(2023,2,20), Amount = 250],
        [Date = #date(2023,3,10), Amount = 150],
        [Date = #date(2023,4,05), Amount = 300],
        [Date = #date(2023,5,12), Amount = 200]
    },
    
    // Complex business rule: discount increases with purchase history
    calculateLTV = List.Accumulate(
        customerPurchases,
        [TotalValue = 0, PurchaseCount = 0, CurrentDiscount = 0],
        (accumulator, purchase) =>
            let
                // Discount increases every 2 purchases, caps at 15%
                newPurchaseCount = accumulator[PurchaseCount] + 1,
                newDiscount = Number.Min(
                    Number.RoundDown(newPurchaseCount / 2) * 0.05,
                    0.15
                ),
                discountedAmount = purchase[Amount] * (1 - accumulator[CurrentDiscount]),
                newTotalValue = accumulator[TotalValue] + discountedAmount
            in
                [
                    TotalValue = newTotalValue,
                    PurchaseCount = newPurchaseCount,
                    CurrentDiscount = newDiscount
                ]
    )
in
    calculateLTV

This returns the final state after processing all purchases. The accumulator maintains three pieces of state: running total value, purchase count, and current discount rate. Each purchase is processed with the discount rate earned from previous purchases.

Tip: The accumulator pattern is perfect when you need to "remember" something from previous iterations. Think running totals, conditional logic based on history, or state machines.

Advanced Accumulation Techniques

Real business scenarios often require more complex state management. Let's examine a sophisticated example: processing financial transactions with different rules based on account history and transaction patterns.

let
    transactions = {
        [Date = #date(2023,1,5), Type = "Deposit", Amount = 1000],
        [Date = #date(2023,1,12), Type = "Withdrawal", Amount = 200],
        [Date = #date(2023,1,18), Type = "Deposit", Amount = 500],
        [Date = #date(2023,1,25), Type = "Withdrawal", Amount = 300],
        [Date = #date(2023,2,2), Type = "Withdrawal", Amount = 150],
        [Date = #date(2023,2,10), Type = "Deposit", Amount = 800]
    },
    
    processTransactions = List.Accumulate(
        transactions,
        [
            Balance = 0,
            OverdraftFees = 0,
            RecentWithdrawals = {},  // Track last 5 withdrawals
            HighValueDeposits = 0    // Count deposits over $500
        ],
        (state, transaction) =>
            let
                isDeposit = transaction[Type] = "Deposit",
                amount = transaction[Amount],
                
                // Update balance
                newBalance = if isDeposit 
                    then state[Balance] + amount 
                    else state[Balance] - amount,
                
                // Calculate overdraft fee if withdrawal creates negative balance
                overdraftFee = if isDeposit or newBalance >= 0 
                    then 0 
                    else 25,
                
                // Track recent withdrawals (last 5)
                updatedWithdrawals = if isDeposit 
                    then state[RecentWithdrawals]
                    else List.LastN(
                        state[RecentWithdrawals] & {[Date = transaction[Date], Amount = amount]},
                        5
                    ),
                
                // Count high-value deposits
                newHighValueCount = if isDeposit and amount > 500
                    then state[HighValueDeposits] + 1
                    else state[HighValueDeposits]
            in
                [
                    Balance = newBalance - overdraftFee,
                    OverdraftFees = state[OverdraftFees] + overdraftFee,
                    RecentWithdrawals = updatedWithdrawals,
                    HighValueDeposits = newHighValueCount
                ]
    )
in
    processTransactions

This example demonstrates several advanced techniques:

• Complex state management: Multiple pieces of related state updated together
• Conditional logic: Different processing based on transaction type
• Historical tracking: Maintaining a sliding window of recent withdrawals
• Business rule application: Overdraft fees, deposit categorization

The power of the accumulator pattern becomes clear when you need to apply business logic that depends on the current state of your data.

Recursive Functions in M

Sometimes you need to process data that references itself - hierarchical structures, tree-like relationships, or iterative calculations where the depth isn't known in advance. This calls for recursion.

Let's build a function that processes an organizational hierarchy, calculating total team sizes including all subordinates:

let
    employeeData = {
        [ID = 1, Name = "CEO", Manager = null, DirectReports = 2],
        [ID = 2, Name = "VP Sales", Manager = 1, DirectReports = 3],
        [ID = 3, Name = "VP Tech", Manager = 1, DirectReports = 5],
        [ID = 4, Name = "Sales Mgr 1", Manager = 2, DirectReports = 2],
        [ID = 5, Name = "Sales Mgr 2", Manager = 2, DirectReports = 3],
        [ID = 6, Name = "Dev Mgr 1", Manager = 3, DirectReports = 4],
        [ID = 7, Name = "Dev Mgr 2", Manager = 3, DirectReports = 6]
    },
    
    // Recursive function to calculate total team size
    CalculateTeamSize = (employeeID as number) =>
        let
            employee = List.First(
                List.Select(employeeData, each [ID] = employeeID)
            ),
            subordinates = List.Select(
                employeeData, 
                each [Manager] = employeeID
            ),
            subordinateTeamSizes = List.Transform(
                subordinates,
                each @CalculateTeamSize([ID])  // Recursive call
            ),
            totalTeamSize = employee[DirectReports] + List.Sum(subordinateTeamSizes)
        in
            totalTeamSize,
    
    // Calculate team size for CEO
    ceoTeamSize = CalculateTeamSize(1)
in
    ceoTeamSize

The recursive pattern works by:

1. Finding the current employee's data
2. Identifying all direct subordinates
3. Recursively calculating each subordinate's team size
4. Summing direct reports plus all subordinate team sizes

Warning: Recursive functions in M can be memory-intensive and slow for large datasets. Always consider whether an iterative approach using List.Generate or List.Accumulate might be more efficient.

Practical Pattern: Processing Nested Data

A common scenario requiring advanced M techniques is flattening and processing nested data structures. Consider JSON data from an API where each record contains arrays of related items:

let
    apiData = {
        [
            CustomerID = "C001",
            CustomerName = "Acme Corp",
            Orders = {
                [OrderID = "O001", Items = {"Widget A", "Widget B"}],
                [OrderID = "O002", Items = {"Widget C"}]
            }
        ],
        [
            CustomerID = "C002", 
            CustomerName = "Beta LLC",
            Orders = {
                [OrderID = "O003", Items = {"Widget A", "Widget D", "Widget E"}]
            }
        ]
    },
    
    // Flatten nested structure with accumulator
    flattenedData = List.Accumulate(
        apiData,
        {},  // Start with empty list
        (accumulator, customer) =>
            let
                customerOrders = List.Accumulate(
                    customer[Orders],
                    {},
                    (orderAccumulator, order) =>
                        let
                            orderItems = List.Transform(
                                order[Items],
                                (item) => [
                                    CustomerID = customer[CustomerID],
                                    CustomerName = customer[CustomerName],
                                    OrderID = order[OrderID],
                                    Item = item
                                ]
                            )
                        in
                            orderAccumulator & orderItems
                )
            in
                accumulator & customerOrders
    )
in
    flattenedData

This creates a flat table where each row represents one item from one order from one customer. The nested accumulation handles the three-level hierarchy efficiently.

Performance Considerations and Best Practices

Advanced M patterns can be powerful but also resource-intensive. Here are key performance considerations:

Choose the right pattern:

• Use List.Accumulate when you need to maintain state across iterations
• Use List.Generate when you're creating new sequences based on conditions
• Consider recursive functions only for truly hierarchical data
• For simple transformations, standard M functions are usually faster

Optimize your state:

• Keep accumulator state as simple as possible
• Avoid storing large lists or tables in state unless necessary
• Consider whether you really need full history or just summary information

Memory management:

// Less efficient - stores entire history
badAccumulator = List.Accumulate(
    data,
    [],  // Stores all processed records
    (state, current) => state & {processed_record}
),

// More efficient - stores only necessary summary
goodAccumulator = List.Accumulate(
    data,
    [Count = 0, Total = 0],  // Stores only summary
    (state, current) => [
        Count = state[Count] + 1,
        Total = state[Total] + current[Amount]
    ]
)

Hands-On Exercise

Let's put these concepts together with a comprehensive example. You'll process a dataset of website sessions, calculating session metrics that depend on user behavior patterns.

Scenario: You have web analytics data where each row represents a page view. You need to:

1. Group page views into sessions (new session after 30+ minutes of inactivity)
2. Calculate session duration, page count, and bounce rate
3. Track returning vs. new visitors

let
    pageViews = {
        [UserID = "U001", Timestamp = #datetime(2023,1,1,9,0,0), Page = "/home"],
        [UserID = "U001", Timestamp = #datetime(2023,1,1,9,5,0), Page = "/products"],
        [UserID = "U001", Timestamp = #datetime(2023,1,1,9,8,0), Page = "/contact"],
        [UserID = "U001", Timestamp = #datetime(2023,1,1,10,45,0), Page = "/home"],  // New session
        [UserID = "U002", Timestamp = #datetime(2023,1,1,10,0,0), Page = "/home"],
        [UserID = "U002", Timestamp = #datetime(2023,1,1,10,2,0), Page = "/about"],
        [UserID = "U001", Timestamp = #datetime(2023,1,1,11,0,0), Page = "/products"]
    },
    
    // Sort by user and timestamp
    sortedViews = List.Sort(pageViews, {"UserID", "Timestamp"}),
    
    // Process into sessions using accumulator
    sessionData = List.Accumulate(
        sortedViews,
        [
            Sessions = {},
            CurrentSession = null,
            SeenUsers = {}
        ],
        (state, pageView) =>
            let
                userId = pageView[UserID],
                currentTime = pageView[Timestamp],
                
                // Check if this starts a new session
                isNewSession = state[CurrentSession] = null 
                    or state[CurrentSession][UserID] <> userId
                    or Duration.TotalMinutes(
                        currentTime - state[CurrentSession][LastActivity]
                    ) > 30,
                
                // Determine if returning user
                isReturningUser = List.Contains(state[SeenUsers], userId),
                
                // Update current session
                updatedSession = if isNewSession then
                    [
                        UserID = userId,
                        SessionStart = currentTime,
                        LastActivity = currentTime,
                        PageCount = 1,
                        IsReturning = isReturningUser,
                        Pages = {pageView[Page]}
                    ]
                else
                    [
                        UserID = state[CurrentSession][UserID],
                        SessionStart = state[CurrentSession][SessionStart],
                        LastActivity = currentTime,
                        PageCount = state[CurrentSession][PageCount] + 1,
                        IsReturning = state[CurrentSession][IsReturning],
                        Pages = state[CurrentSession][Pages] & {pageView[Page]}
                    ],
                
                // Update sessions list
                updatedSessions = if isNewSession and state[CurrentSession] <> null then
                    state[Sessions] & {
                        state[CurrentSession] & [
                            Duration = Duration.TotalMinutes(
                                state[CurrentSession][LastActivity] - 
                                state[CurrentSession][SessionStart]
                            ),
                            IsBounce = state[CurrentSession][PageCount] = 1
                        ]
                    }
                else
                    state[Sessions],
                
                // Update seen users
                updatedSeenUsers = if not isReturningUser then
                    state[SeenUsers] & {userId}
                else
                    state[SeenUsers]
            in
                [
                    Sessions = updatedSessions,
                    CurrentSession = updatedSession,
                    SeenUsers = updatedSeenUsers
                ]
    ),
    
    // Add the final session
    finalSessions = sessionData[Sessions] & {
        sessionData[CurrentSession] & [
            Duration = Duration.TotalMinutes(
                sessionData[CurrentSession][LastActivity] - 
                sessionData[CurrentSession][SessionStart]
            ),
            IsBounce = sessionData[CurrentSession][PageCount] = 1
        ]
    }
in
    finalSessions

Work through this example step by step. Notice how the accumulator maintains complex state: current session details, completed sessions, and user tracking. Each page view either extends the current session or starts a new one based on time gaps.

Common Mistakes & Troubleshooting

Infinite recursion: The most dangerous mistake with recursive functions is forgetting the base case or creating conditions that never terminate.

// BAD - no termination condition
BadRecursion = (x) => BadRecursion(x + 1),

// GOOD - clear base case
GoodRecursion = (x) => if x <= 0 then 0 else x + GoodRecursion(x - 1)

Memory bloat in accumulators: Storing too much data in your accumulator state can cause performance problems.

// BAD - stores entire history
List.Accumulate(data, {}, (state, current) => state & {current}),

// GOOD - stores only what you need
List.Accumulate(data, 0, (state, current) => state + current[Value])

Incorrect state updates: Remember that records are immutable. You must create new records, not modify existing ones.

// BAD - trying to modify existing record
(state, current) => [state[Count] = state[Count] + 1],  // Error!

// GOOD - creating new record
(state, current) => [Count = state[Count] + 1, Total = state[Total]]

Type errors in complex iterations: M's type system can be strict with complex nested operations. Be explicit about types when needed.

// Add type annotations for clarity
(state as record, current as record) as record => [
    Count = state[Count] + 1,
    Total = state[Total] + current[Amount]
]

Summary & Next Steps

You've now mastered M's most powerful iterative and recursive patterns. These techniques open up entirely new categories of data transformation problems that you can solve elegantly within Power Query.

The key concepts to remember:

• List.Generate creates sequences through iteration
• List.Accumulate transforms data while maintaining state
• Recursive functions handle hierarchical data structures
• The accumulator pattern is perfect for stateful transformations
• Performance matters - choose the right pattern for your data size

With these tools, you can tackle complex business logic, process nested data structures, and build sophisticated data transformations that would be difficult or impossible with standard Power Query operations.

Next, explore how these patterns combine with Power Query's other advanced features like custom connectors and complex data type handling. Consider practicing with your own datasets - find places where you're using multiple steps that could be simplified into a single iterative transformation.