M — Power Query's formula language — represents all data using three container types: Tables, Lists, and Records. Learn how each one works, how to navigate inside them with M syntax, and how they nest together to form the complex structures you see in JSON and API data.
You've imported a JSON file into Power Query and suddenly the column shows "List" or "Record" instead of the text values you expected. You click the expand icon, get a dozen new columns, and wonder what just happened. Or maybe you've been happily using Power Query's GUI buttons for months but you've hit a wall — the button you need doesn't exist, and you have to write some M code yourself. Either way, you're facing the same fundamental gap: you don't yet have a mental model for how M thinks about data.
M, the formula language behind Power Query, doesn't see data the way a spreadsheet does. It has three core container types — Tables, Lists, and Records — and every transformation you perform is ultimately an operation on one or more of these structures. Once you understand what each one is, how M navigates inside it, and how they nest inside each other, the language clicks into place. That JSON expand button makes sense. The formula bar stops being intimidating. And you can start writing logic that the GUI could never produce on its own.
By the end of this lesson, you'll be able to look at any value in Power Query's preview pane, know exactly what type it is, navigate into it using M syntax, and manipulate all three structures confidently.
What you'll learn:
This lesson assumes you know how to open the Power Query Editor and run a blank query. If you're brand new to Power Query, start with Power Query 101: Connect, Transform, Load to get your bearings. It also helps to have read Understanding the M Formula Language: Syntax, Data Types, and Expression Basics so you're comfortable with M expressions generally, though it isn't strictly required.
Before diving into syntax, let's build a clear mental model.
Think of a Table as what you normally think of when you think of data: rows and columns, like a spreadsheet or a database result set. It has column headers and zero or more rows of data. This is the final form Power Query loads into your data model.
A List is an ordered sequence of values, like a single column with no header. It's just values, one after another, with positions starting at zero. Lists can contain anything: numbers, text, dates, or even other Lists and Records.
A Record is a named collection of fields — like a single row with labeled slots. Instead of positions, you navigate it by name. If a List is a column, a Record is a row.
These three types are not interchangeable, but they compose naturally. A Table is essentially a List of Records (each record is a row), where every record shares the same field names (the column headers). This composability is what makes M both powerful and occasionally confusing.
Key insight: Every time Power Query shows you a cell containing the word "List," "Record," or "Table," that cell holds one of these container types as a value. It's a structured value nested inside your outer table — and you can always drill into it.
A Table is the primary output type of Power Query — it's what gets loaded into Power BI or Excel. But Tables aren't just the final result; they're also values you can create, pass around, and operate on mid-query.
You can construct a Table from scratch using #table(). Open a blank query (in Power Query Editor, go to Home → New Source → Blank Query, then open the Advanced Editor) and paste this:
#table(
{"OrderID", "Customer", "Amount"},
{
{1001, "Contoso Ltd", 4500.00},
{1002, "Fabrikam Inc", 2100.75},
{1003, "Contoso Ltd", 8900.00}
}
)
#table() takes two arguments: a list of column names, and a list of rows (each row is itself a list of values). Run this and you'll see a three-row, three-column table in the preview pane — a real table created entirely in M, without connecting to any external data source.
To get a specific cell, you use two navigation operators in sequence: first select the row by index (zero-based), then select the field by name.
let
SalesData = #table(
{"OrderID", "Customer", "Amount"},
{
{1001, "Contoso Ltd", 4500.00},
{1002, "Fabrikam Inc", 2100.75},
{1003, "Contoso Ltd", 8900.00}
}
),
// Get the first row as a Record
FirstRow = SalesData{0},
// Get the Amount field from that record
FirstAmount = FirstRow[Amount]
in
FirstAmount
This returns 4500. Notice the two-step pattern: SalesData{0} extracts row zero as a Record, and then [Amount] accesses the Amount field within that Record. You'll see this curly-brace / square-bracket combination constantly in M code.
You can also pull an entire column as a List using the Table.Column() function:
Table.Column(SalesData, "Customer")
// Returns: {"Contoso Ltd", "Fabrikam Inc", "Contoso Ltd"}
The M standard library has hundreds of Table.* functions. The most useful ones for everyday work:
Table.SelectRows(table, each [Amount] > 3000) — filter rows by a conditionTable.AddColumn(table, "Tax", each [Amount] * 0.1) — add a calculated columnTable.Sort(table, {{"Amount", Order.Descending}}) — sort rowsTable.Group(table, {"Customer"}, {"TotalAmount", each List.Sum([Amount]), type number}) — aggregateTip: When you click a button in the Power Query GUI, it writes one of these
Table.*functions into a new step behind the scenes. You can always click "View → Advanced Editor" to see the M code any button press generates. This is one of the best ways to learn M — use the GUI, then read what it wrote.
A List is enclosed in curly braces {}. Every value inside is separated by a comma.
{"London", "Paris", "Tokyo", "Sydney"}
That's a valid, complete M expression — it evaluates to a List of four text values. Lists are zero-indexed, meaning the first item is at position 0, not 1.
let
Cities = {"London", "Paris", "Tokyo", "Sydney"},
SecondCity = Cities{1}
in
SecondCity
// Returns: "Paris"
Lists don't have to contain the same type of value — M won't stop you from mixing types — but in practice you'll usually work with homogeneous lists (all numbers, all text, etc.).
Lists appear everywhere in Power Query once you go beyond flat CSV files:
Text.Combine() on a ListTable.ColumnNames() returns a List)#table() constructor are a List of ListsUnderstanding Lists is especially critical when unpacking nested JSON and XML structures, because those nested arrays land in your table as List-typed cells.
List.Count({"a", "b", "c"}) // 3
List.Sum({100, 200, 300}) // 600
List.Distinct({"Yes", "No", "Yes"}) // {"Yes", "No"}
List.Contains({"Yes", "No"}, "Maybe") // false
List.Sort({3, 1, 4, 1, 5}, Order.Ascending) // {1, 1, 3, 4, 5}
List.Transform({1, 2, 3}, each _ * 10) // {10, 20, 30}
List.Select({1, 2, 3, 4, 5}, each _ > 2) // {3, 4, 5}
List.Transform() and List.Select() are worth memorizing. They work like map and filter in other languages — they let you apply a function to every item or filter items by a condition, without a loop.
Note: The underscore
_ineach _ * 10represents "the current item." You'll also seeeach [FieldName]in Table contexts, where the current item is a Record and you're accessing its field. Both are shorthand for a full function expression.
You can generate lists without typing every value:
{1..10} // {1, 2, 3, 4, 5, 6, 7, 8, 9, 10}
{"A".."E"} // Not valid — M range only works with numbers
List.Dates(
#date(2024, 1, 1),
12,
#duration(31, 0, 0, 0)
) // 12 dates, 31 days apart
The {1..10} range syntax is a neat shortcut you'll use when building a dynamic date dimension table or generating row indices.
A Record uses square brackets [] with named field-value pairs separated by commas:
[
OrderID = 1001,
Customer = "Contoso Ltd",
Amount = 4500.00,
OrderDate = #date(2024, 3, 15)
]
This is a single Record with four fields. Records have no inherent order — you access values by name, not position.
let
Order = [
OrderID = 1001,
Customer = "Contoso Ltd",
Amount = 4500.00
],
CustomerName = Order[Customer]
in
CustomerName
// Returns: "Contoso Ltd"
Here's the connection that ties everything together: each row in a Table is a Record. When you use each in a Table function, the implicit _ is a Record representing the current row, and [ColumnName] accesses a field in that row-record.
Table.SelectRows(
SalesData,
each [Amount] > 3000
)
In plain English: "For each row (Record) in SalesData, keep it if its Amount field is greater than 3000." The each [Amount] syntax is shorthand for (_) => _[Amount] — a function that takes the current row-record and returns the value of its Amount field.
This is why the each [FieldName] pattern appears in practically every non-trivial M formula. You're not doing magic — you're accessing fields on Records that represent rows.
Record.FieldNames(myRecord) // Returns a List of field names
Record.FieldValues(myRecord) // Returns a List of values
Record.HasFields(myRecord, "Customer") // true or false
Record.AddField(myRecord, "Discount", 0.10) // Returns a new Record with the added field
Record.RemoveFields(myRecord, {"Amount"}) // Returns a Record without the Amount field
Record.ToTable(myRecord) // Converts to a 2-column Table: Name, Value
Record.ToTable() is particularly handy when you've received data as a Record (say, from an API response) and you need to pivot it into a proper table for reporting.
Warning: Records in M are immutable — functions like
Record.AddField()don't modify the original Record; they return a new one. This is true of all M values. If you assign a new Record to the same step name, you're creating a fresh value, not editing the old one. Forgetting this leads to confusing bugs where you "add a field" but the original step downstream still shows the old structure.
The real power of understanding Tables, Lists, and Records comes when you see how they compose.
Conceptually:
// This Table:
#table({"Name", "Score"}, {{"Alice", 92}, {"Bob", 88}})
// Is equivalent to a List of Records like:
{
[Name = "Alice", Score = 92],
[Name = "Bob", Score = 88]
}
You can convert between these representations:
// Table to List of Records
Table.ToRecords(myTable)
// List of Records to Table
Table.FromRecords(myListOfRecords)
Complex data sources — especially APIs, JSON files, and SharePoint list exports — produce tables where cells contain Lists or Records. When loading data from APIs and web pages, this is almost guaranteed.
Imagine an Orders table where each order has a nested list of line items:
#table(
{"OrderID", "Customer", "LineItems"},
{
{1001, "Contoso Ltd", {
[Product = "Widget A", Qty = 5, Price = 100],
[Product = "Widget B", Qty = 2, Price = 250]
}},
{1002, "Fabrikam Inc", {
[Product = "Widget C", Qty = 10, Price = 75]
}}
}
)
The LineItems column contains a List of Records. In Power Query's preview pane, you'd see each cell show "List." Clicking the expand icon on that column tells Power Query to apply Table.ExpandListColumn() followed by Table.ExpandRecordColumn() — which is exactly what happens when you use the expand buttons in the GUI on a JSON import.
Key insight: The expand button in the Power Query UI is literally just navigating into nested Lists and Records. Understanding the underlying structure means you can handle cases where the GUI expand doesn't give you what you want — because you can write the expansion logic yourself using
Table.ExpandListColumn(),Table.ExpandRecordColumn(), orList.Transform().
Let's put it all together with a realistic scenario. You've received a product catalog from a vendor as a structured M expression (simulating what you'd get from a JSON API). Build this in a new blank query using the Advanced Editor:
let
// Step 1: Simulate raw API data as a List of Records
RawData = {
[
SKU = "PRD-001",
Name = "Ergonomic Chair",
Attributes = [Color = "Black", Material = "Mesh", WeightKg = 12.5],
Tags = {"office", "furniture", "ergonomic"}
],
[
SKU = "PRD-002",
Name = "Standing Desk",
Attributes = [Color = "Oak", Material = "Wood", WeightKg = 35.0],
Tags = {"office", "furniture", "height-adjustable"}
],
[
SKU = "PRD-003",
Name = "Monitor Stand",
Attributes = [Color = "Silver", Material = "Aluminum", WeightKg = 2.1],
Tags = {"office", "accessories"}
]
},
// Step 2: Convert List of Records to a Table
AsTable = Table.FromRecords(RawData),
// Step 3: Expand the nested Attributes record into columns
ExpandAttributes = Table.ExpandRecordColumn(
AsTable,
"Attributes",
{"Color", "Material", "WeightKg"}
),
// Step 4: Extract the first tag from the Tags list as a new column
AddFirstTag = Table.AddColumn(
ExpandAttributes,
"PrimaryTag",
each [Tags]{0},
type text
),
// Step 5: Count the number of tags per product
AddTagCount = Table.AddColumn(
AddFirstTag,
"TagCount",
each List.Count([Tags]),
type number
),
// Step 6: Remove the Tags column (it's done its job)
Result = Table.RemoveColumns(AddTagCount, {"Tags"})
in
Result
Work through each step and observe the intermediate results. After Step 2, you have a proper table but the Attributes and Tags columns still show "Record" and "List." After Step 3, Attributes has been expanded. Step 4 shows you navigating into a List cell with {0}. Step 5 demonstrates calling a List function (List.Count) on a cell value.
This mirrors exactly what you'd do after importing a product catalog from a REST API — the structure is the same, just arriving via Web.Contents() instead of a hardcoded literal.
Mistake 1: Using {1} when you mean the second item, forgetting zero-indexing
M lists are zero-indexed. myList{0} is the first item, myList{1} is the second. If you expect {1} to return the first item and get the second instead, this is why. There's no setting to change it — just internalize it early.
Mistake 2: Confusing [] for Records with [] for column references in each expressions
[Amount] inside an each expression means "the Amount field of the current row-Record." But [Amount = 0] when used as a second argument to something like Table.SelectRows() is a Record literal being used as an options parameter. Both use square brackets, but in different syntactic positions. When in doubt, look at whether it has an = inside — a Record literal does, a field access doesn't.
Mistake 3: Trying to modify a Record or List in place
As noted earlier, M values are immutable. If you call Record.AddField(myRecord, "NewField", "value") but don't assign the result to a new step, nothing happens. Always capture the return value.
Mistake 4: Expanding a List column when you needed to expand a Record column
Table.ExpandListColumn() and Table.ExpandRecordColumn() do very different things. Applying the wrong one gives you an error or an unexpected structure. First check what type the column actually contains — hover over a cell in the preview pane and Power Query will show the type. List cells show a list icon; Record cells show a record icon.
Warning: When you expand a List column, Power Query creates one new row per list item — your row count multiplies. When you expand a Record column, Power Query creates new columns. Mixing these up in a pipeline produces wildly incorrect row counts that can be hard to debug later. If your row count looks wrong after an expand, this is the first thing to check. For more on handling complex nested structures, see the guide on unpacking nested JSON and XML structures.
Mistake 5: Hardcoding field names in Table.ExpandRecordColumn() when the schema might change
If the Records in your column might have different fields across rows, hardcoding {"Color", "Material", "WeightKg"} will silently drop any other fields. To expand all fields dynamically, first collect all unique field names:
AllFields = List.Distinct(
List.Combine(
List.Transform(
Table.Column(AsTable, "Attributes"),
each Record.FieldNames(_)
)
)
)
Then pass AllFields as the field names argument. This is the handling dynamic schema changes approach applied to nested Records.
You've now got the core mental model for how M represents data. Let's recap the key ideas:
{rowIndex} (returns a Record), then into a cell with [ColumnName].{}. Access items by zero-based position with {index}. Manipulate with List.Transform(), List.Select(), List.Count(), and friends.[]. Access fields by name with [FieldName]. Manipulate with Record.AddField(), Record.FieldNames(), Record.ToTable(), and others.each [FieldName] pattern you see everywhere is shorthand for a function that receives the current row as a Record and accesses a named field.With this foundation solid, you're ready to go further. Consider exploring how to add custom columns and conditional logic using the Record-navigation skills you've built here, or see how these structures play out in a full pipeline architecture in building multi-stage staging architectures in Power Query. If you're working with aggregations, grouping and aggregating data in Power Query uses all three container types under the hood — you'll read that code with fresh eyes now.
The best next step, though, is simply to open the Advanced Editor on a query you already have and trace through what each step returns. Now that you can name what you're looking at — Table, List, Record — the code will start to read itself.