List and Record Operations in M: Transform, Select, and Combine

Imagine you're working with customer data where each customer record contains multiple phone numbers, order histories, and preference settings. In Excel, you'd be copying and pasting across multiple worksheets, creating VLOOKUP formulas, and manually restructuring data for hours. In Power Query's M language, you can transform, select, and combine this complex nested data in just a few lines of code.

Lists and records are the fundamental building blocks of structured data in M. A list is an ordered collection of values (like an array), while a record is a collection of named fields (like a row in a table or an object in JSON). Understanding how to manipulate these data structures is essential for any serious data transformation work in Power Query. Whether you're cleaning messy API responses, restructuring imported data, or combining information from multiple sources, list and record operations are your primary tools.

What you'll learn:

• How to transform list elements using List.Transform and custom functions
• How to select specific items from lists and fields from records
• How to combine multiple lists and records into unified data structures
• How to work with nested lists and records in real-world scenarios
• How to troubleshoot common issues with list and record operations

Prerequisites

Before diving into list and record operations, you should understand:

• Basic M syntax and data types
• How to create simple custom functions in Power Query
• The difference between lists, records, and tables in M
• How to access the Advanced Editor in Power Query

Understanding Lists and Records in M

Let's start with the fundamentals. In M, a list is an ordered sequence of values enclosed in curly braces, while a record is a collection of field-value pairs enclosed in square brackets.

// A simple list of customer names
CustomerNames = {"Alice Johnson", "Bob Smith", "Carol Williams"}

// A record representing a customer
Customer = [
    Name = "Alice Johnson",
    Email = "alice@email.com",
    Phone = "555-0123",
    City = "Chicago"
]

// A list of records (common structure)
Customers = {
    [Name = "Alice Johnson", Email = "alice@email.com", Phone = "555-0123"],
    [Name = "Bob Smith", Email = "bob@email.com", Phone = "555-0456"],
    [Name = "Carol Williams", Email = "carol@email.com", Phone = "555-0789"]
}

The power comes when you need to manipulate these structures. Real-world data is rarely as clean as what we just created. You might receive customer data where phone numbers are stored as lists within each record, or where you need to extract specific information from nested structures.

Transforming Lists with List.Transform

The List.Transform function is your primary tool for applying operations to every item in a list. It takes two arguments: the list to transform and a function to apply to each element.

Let's work with a realistic scenario. Suppose you have a list of customer email addresses, but they're inconsistently formatted—some have uppercase letters, extra spaces, or missing domains. Here's how you'd clean them:

// Raw email data as it might come from a system
RawEmails = {
    "  ALICE@COMPANY.COM  ",
    "bob@Company.com",
    "  carol@COMPANY.COM",
    "dave@company.COM  "
}

// Clean and standardize the email addresses
CleanEmails = List.Transform(
    RawEmails,
    each Text.Lower(Text.Trim(_))
)

// Result: {"alice@company.com", "bob@company.com", "carol@company.com", "dave@company.com"}

The each keyword creates an anonymous function where _ represents each item in the list. Text.Trim removes leading and trailing spaces, while Text.Lower converts everything to lowercase.

You can also use more complex transformations. Here's how you might extract domain names from those email addresses:

// Extract domain names from cleaned emails
DomainNames = List.Transform(
    CleanEmails,
    each Text.AfterDelimiter(_, "@")
)

// Result: {"company.com", "company.com", "company.com", "company.com"}

For even more complex transformations, you can define custom functions and use them with List.Transform:

// Custom function to format phone numbers
FormatPhoneNumber = (phoneNumber as text) as text =>
    let
        // Remove all non-digit characters
        DigitsOnly = Text.Select(phoneNumber, {"0".."9"}),
        // Format as (XXX) XXX-XXXX
        Formatted = "(" & Text.Start(DigitsOnly, 3) & ") " & 
                   Text.Middle(DigitsOnly, 3, 3) & "-" & 
                   Text.End(DigitsOnly, 4)
    in
        Formatted

// Apply to a list of messy phone numbers
MessyPhones = {"555.123.4567", "(555) 234-5678", "555-345-6789", "5554567890"}
FormattedPhones = List.Transform(MessyPhones, FormatPhoneNumber)

// Result: {"(555) 123-4567", "(555) 234-5678", "(555) 345-6789", "(555) 456-7890"}

Selecting from Lists and Records

Selection operations let you extract specific elements or fields based on criteria. For lists, you have several options depending on what you need.

Selecting from Lists

The List.Select function filters a list based on a condition:

// Sample customer ages
CustomerAges = {25, 34, 19, 42, 38, 17, 29, 55, 31}

// Select customers who are 21 or older
AdultCustomers = List.Select(CustomerAges, each _ >= 21)
// Result: {25, 34, 42, 38, 29, 55, 31}

// Select customers in their thirties
ThirtysomethingCustomers = List.Select(CustomerAges, each _ >= 30 and _ < 40)
// Result: {34, 38, 31}

For positional selection, use indexing or functions like List.First, List.Last, and List.Range:

// Get specific positions
FirstCustomer = List.First(CustomerNames)      // "Alice Johnson"
LastCustomer = List.Last(CustomerNames)        // "Carol Williams"
MiddleCustomer = CustomerNames{1}              // "Bob Smith" (zero-indexed)

// Get a range of items
FirstTwoCustomers = List.Range(CustomerNames, 0, 2)
// Result: {"Alice Johnson", "Bob Smith"}

Selecting from Records

Record selection uses field names. You can access fields using dot notation or the Record.Field function:

// Sample customer record
Customer = [
    Name = "Alice Johnson",
    Email = "alice@email.com",
    Phone = "555-0123",
    City = "Chicago",
    State = "IL",
    ZipCode = "60601"
]

// Different ways to select fields
CustomerName = Customer[Name]                    // "Alice Johnson"
CustomerEmail = Record.Field(Customer, "Email") // "alice@email.com"

// Select multiple fields into a new record
ContactInfo = [
    Name = Customer[Name],
    Phone = Customer[Phone],
    Email = Customer[Email]
]

You can also use Record.SelectFields to create a new record with only specified fields:

// Select only contact-related fields
ContactFields = {"Name", "Phone", "Email"}
CustomerContact = Record.SelectFields(Customer, ContactFields)

// Result: [Name = "Alice Johnson", Phone = "555-0123", Email = "alice@email.com"]

Combining Lists and Records

Real-world data transformation often requires combining multiple data sources. M provides several functions for this purpose.

Combining Lists

The most straightforward combination is concatenation with the & operator or List.Combine:

// Two separate customer lists
WestCoastCustomers = {"Alice Johnson", "Bob Smith"}
EastCoastCustomers = {"Carol Williams", "Dave Brown"}

// Combine using the & operator
AllCustomers = WestCoastCustomers & EastCoastCustomers
// Result: {"Alice Johnson", "Bob Smith", "Carol Williams", "Dave Brown"}

// Combine multiple lists at once
RegionalLists = {WestCoastCustomers, EastCoastCustomers, {"Eve Davis", "Frank Miller"}}
AllRegionalCustomers = List.Combine(RegionalLists)
// Result: {"Alice Johnson", "Bob Smith", "Carol Williams", "Dave Brown", "Eve Davis", "Frank Miller"}

For more sophisticated combinations, you might need to merge based on conditions. Here's how you might combine customer lists while removing duplicates:

// Lists with potential duplicates
List1 = {"Alice", "Bob", "Carol"}
List2 = {"Bob", "Carol", "Dave"}

// Combine and remove duplicates
CombinedUnique = List.Distinct(List1 & List2)
// Result: {"Alice", "Bob", "Carol", "Dave"}

Combining Records

Record combination typically involves merging fields from multiple records. The & operator works for records too, with the right record's fields taking precedence for duplicate field names:

// Basic customer information
BasicInfo = [Name = "Alice Johnson", Email = "alice@email.com"]

// Additional customer details
AdditionalInfo = [Phone = "555-0123", City = "Chicago", Email = "alice.johnson@email.com"]

// Combine records (AdditionalInfo email will override BasicInfo email)
FullCustomerInfo = BasicInfo & AdditionalInfo
// Result: [Name = "Alice Johnson", Email = "alice.johnson@email.com", Phone = "555-0123", City = "Chicago"]

For more control over field merging, you can use Record.Combine:

// Multiple records to combine
Records = {
    [Name = "Alice Johnson"],
    [Email = "alice@email.com", Phone = "555-0123"],
    [City = "Chicago", State = "IL"]
}

// Combine all records into one
CombinedRecord = Record.Combine(Records)
// Result: [Name = "Alice Johnson", Email = "alice@email.com", Phone = "555-0123", City = "Chicago", State = "IL"]

Working with Nested Structures

Real-world data often contains nested lists and records. For example, each customer might have multiple phone numbers, addresses, or order histories. Here's how to handle these complex structures.

Consider this realistic customer data structure:

ComplexCustomer = [
    Name = "Alice Johnson",
    Email = "alice@email.com",
    PhoneNumbers = {"555-0123", "555-0124", "555-0125"},
    Addresses = {
        [Type = "Home", Street = "123 Main St", City = "Chicago", State = "IL"],
        [Type = "Work", Street = "456 Oak Ave", City = "Chicago", State = "IL"]
    },
    OrderHistory = {
        [Date = #date(2024, 1, 15), Amount = 125.50, Status = "Completed"],
        [Date = #date(2024, 2, 3), Amount = 89.99, Status = "Completed"],
        [Date = #date(2024, 2, 20), Amount = 234.75, Status = "Pending"]
    }
]

To extract and transform nested data, you combine selection and transformation operations:

// Extract the primary phone number (first in the list)
PrimaryPhone = List.First(ComplexCustomer[PhoneNumbers])

// Get all home addresses
HomeAddresses = List.Select(
    ComplexCustomer[Addresses],
    each [Type] = "Home"
)

// Calculate total order value
TotalOrderValue = List.Sum(
    List.Transform(
        ComplexCustomer[OrderHistory],
        each [Amount]
    )
)

// Get recent orders (last 30 days from February 25, 2024)
RecentOrders = List.Select(
    ComplexCustomer[OrderHistory],
    each [Date] >= #date(2024, 1, 25)
)

For more complex nested transformations, you might need to work with lists of records that contain other lists:

// Multiple customers with nested data
CustomerDatabase = {
    [
        Name = "Alice Johnson",
        Orders = {
            [Product = "Laptop", Quantity = 1, Price = 899.99],
            [Product = "Mouse", Quantity = 2, Price = 29.99]
        }
    ],
    [
        Name = "Bob Smith",
        Orders = {
            [Product = "Tablet", Quantity = 1, Price = 549.99],
            [Product = "Case", Quantity = 1, Price = 39.99]
        }
    ]
}

// Extract all products ordered by all customers
AllProducts = List.Transform(
    CustomerDatabase,
    each List.Transform([Orders], each [Product])
)
// This gives us nested lists: {{"Laptop", "Mouse"}, {"Tablet", "Case"}}

// Flatten to a single list of all products
FlatProductList = List.Combine(AllProducts)
// Result: {"Laptop", "Mouse", "Tablet", "Case"}

// Get unique products ordered
UniqueProducts = List.Distinct(FlatProductList)

Advanced Combination Techniques

Sometimes you need to combine data based on complex business logic. Here are some advanced patterns you'll encounter in real-world scenarios.

Conditional Merging

You might need to merge records only when certain conditions are met:

MergeCustomerData = (baseRecord as record, updateRecord as record) as record =>
    let
        // Only merge if the update record has a valid email
        ShouldMerge = Record.HasFields(updateRecord, "Email") and 
                     updateRecord[Email] <> null and 
                     updateRecord[Email] <> "",
        
        Result = if ShouldMerge then baseRecord & updateRecord else baseRecord
    in
        Result

// Example usage
BaseCustomer = [Name = "Alice Johnson", Phone = "555-0123"]
GoodUpdate = [Email = "alice@email.com", City = "Chicago"]
BadUpdate = [Email = "", City = "Chicago"]

MergedGood = MergeCustomerData(BaseCustomer, GoodUpdate)
// Result: [Name = "Alice Johnson", Phone = "555-0123", Email = "alice@email.com", City = "Chicago"]

MergedBad = MergeCustomerData(BaseCustomer, BadUpdate)
// Result: [Name = "Alice Johnson", Phone = "555-0123"] (unchanged)

Aggregating Nested Data

When working with nested structures, you often need to aggregate data across the nested elements:

// Calculate customer metrics from order history
CalculateCustomerMetrics = (customer as record) as record =>
    let
        Orders = customer[OrderHistory],
        TotalSpent = List.Sum(List.Transform(Orders, each [Amount])),
        OrderCount = List.Count(Orders),
        AverageOrder = if OrderCount > 0 then TotalSpent / OrderCount else 0,
        LastOrderDate = List.Max(List.Transform(Orders, each [Date]))
    in
        customer & [
            TotalSpent = TotalSpent,
            OrderCount = OrderCount,
            AverageOrderValue = AverageOrder,
            LastOrderDate = LastOrderDate
        ]

// Apply to our complex customer
EnrichedCustomer = CalculateCustomerMetrics(ComplexCustomer)

Hands-On Exercise

Let's put these concepts together with a realistic scenario. Imagine you're working with customer data from an e-commerce system where each customer has multiple contact methods and purchase history.

// Raw customer data as it might come from a system
RawCustomerData = {
    [
        ID = 1001,
        Name = "  ALICE JOHNSON  ",
        Contacts = {
            [Type = "email", Value = "ALICE@COMPANY.COM"],
            [Type = "phone", Value = "5551234567"],
            [Type = "phone", Value = "5559876543"]
        },
        Purchases = {
            [Date = "2024-01-15", Amount = "125.50"],
            [Date = "2024-02-03", Amount = "89.99"]
        }
    },
    [
        ID = 1002,
        Name = "bob smith",
        Contacts = {
            [Type = "email", Value = "bob@company.com"],
            [Type = "phone", Value = "(555) 345-6789"]
        },
        Purchases = {
            [Date = "2024-01-20", Amount = "234.75"]
        }
    ]
}

// Step 1: Clean customer names
CleanedCustomers = List.Transform(
    RawCustomerData,
    each _ & [Name = Text.Proper(Text.Trim([Name]))]
)

// Step 2: Extract and format primary contact information
ProcessContacts = (contacts as list) as record =>
    let
        EmailContact = List.First(
            List.Select(contacts, each [Type] = "email"),
            null
        ),
        PhoneContacts = List.Select(contacts, each [Type] = "phone"),
        PrimaryPhone = List.First(PhoneContacts, null),
        
        Email = if EmailContact <> null then Text.Lower(EmailContact[Value]) else null,
        Phone = if PrimaryPhone <> null then PrimaryPhone[Value] else null
    in
        [Email = Email, Phone = Phone]

// Step 3: Calculate purchase metrics
CalculatePurchaseMetrics = (purchases as list) as record =>
    let
        Amounts = List.Transform(purchases, each Number.FromText([Amount])),
        TotalSpent = List.Sum(Amounts),
        PurchaseCount = List.Count(purchases),
        AverageSpent = if PurchaseCount > 0 then TotalSpent / PurchaseCount else 0
    in
        [
            TotalSpent = TotalSpent,
            PurchaseCount = PurchaseCount,
            AverageSpent = AverageSpent
        ]

// Step 4: Combine everything into enriched customer records
EnrichedCustomers = List.Transform(
    CleanedCustomers,
    each 
        let
            BasicInfo = [ID = [ID], Name = [Name]],
            ContactInfo = ProcessContacts([Contacts]),
            PurchaseInfo = CalculatePurchaseMetrics([Purchases])
        in
            BasicInfo & ContactInfo & PurchaseInfo
)

This exercise demonstrates the full pipeline: transforming nested structures, selecting relevant data, and combining multiple pieces of information into a clean, flat structure suitable for analysis.

Common Mistakes & Troubleshooting

When working with list and record operations, several common issues can trip up beginners:

Mistake 1: Confusing List and Record Syntax Lists use curly braces {}, records use square brackets []. Mixing these up is a common error:

// Wrong - trying to use record syntax for a list
WrongList = [1, 2, 3]  // This creates a record, not a list

// Correct
CorrectList = {1, 2, 3}

Mistake 2: Forgetting to Handle Null Values When selecting from lists or records, you might encounter null values that cause errors:

// Problematic - will error if Customer[Phone] is null
BadPhoneFormat = Text.Length(Customer[Phone])

// Better - handle null values explicitly
SafePhoneFormat = if Customer[Phone] <> null then Text.Length(Customer[Phone]) else 0

Mistake 3: Index Out of Range Errors Accessing list items by index without checking list length:

// Dangerous - will error if list has fewer than 3 items
ThirdItem = CustomerList{2}

// Safer approach
ThirdItem = if List.Count(CustomerList) > 2 then CustomerList{2} else null

Mistake 4: Incorrect Field Name References M is case-sensitive, and field names must match exactly:

Customer = [Name = "Alice", email = "alice@email.com"]

// Wrong - field name case doesn't match
BadEmail = Customer[Email]  // Error: field doesn't exist

// Correct
GoodEmail = Customer[email]

Tip: Use the Record.FieldNames() function to see all available field names in a record when debugging.

Troubleshooting Strategy: When your list or record operations aren't working as expected:

1. Check data types using Value.Type() to ensure you're working with lists and records where you expect them
2. Use step-by-step evaluation in the Power Query editor to see intermediate results
3. Add null checks before performing operations on potentially missing data
4. Verify field names are spelled correctly and match case

Summary & Next Steps

You now have a solid foundation in list and record operations in M. You've learned how to:

• Transform list elements using List.Transform with both simple and complex functions
• Select specific items from lists using conditions and positional access
• Extract fields from records using multiple syntax approaches
• Combine lists and records using various concatenation and merging techniques
• Work with nested data structures common in real-world scenarios
• Handle complex business logic in data transformation workflows

These operations form the backbone of most data transformation tasks in Power Query. Whether you're cleaning imported data, restructuring API responses, or preparing data for analysis, you'll use these patterns repeatedly.

Next steps in your M language journey:

• Explore table operations that build on list and record concepts
• Learn about error handling and data type conversion
• Study advanced function composition techniques
• Practice with more complex nested data scenarios from JSON and XML sources

The key to mastering these operations is practice with real data. Start applying these techniques to your own datasets, and you'll quickly develop intuition for which operations to use in different scenarios.