Record.SelectFields Function in Power Query M (5 Easy Examples)

If you want to keep only a few fields from a record, and drop the rest, the Record.SelectFields function is what you reach for. It returns a new record with just the fields you name, in the order you list them. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

Table of Contents

Syntax of Record.SelectFields Function

Record.SelectFields(record as record, fields as any, optional missingField as nullable number) as record

where

record (required, record). The source record you want to pull fields from.
fields (required, any). The field names to keep, given as a list like {"Item","Price"}. A single name as plain text also works. The output follows this order, so this argument controls both which fields you keep and what order they come out in.
missingField (optional, nullable number). Controls what happens when you name a field that the record does not have. Leave it out and a missing name raises an error. Pass MissingField.UseNull to include the field with a null value, or MissingField.Ignore to silently drop it from the result.

Returns: a new record containing only the named fields, in the order you listed them. The original record is left unchanged.

In plain terms, you hand it a record and a list of field names, and it gives you back a smaller record with just those fields.

Example 1: Keep two fields from a record

Say you have an order record with four fields and you only want the item and its price.

let
    Source = Record.SelectFields([OrderID=1,CustomerID=42,Item="Fishing rod",Price=100.0],{"Item","Price"})
in
    Source

Result: [Item = "Fishing rod", Price = 100]

The OrderID and CustomerID fields are dropped, leaving a record with just Item and Price.

Example 2: Reorder fields by listing them in a new order

The output order follows your field list, not the record’s original order. So you can use this to rearrange fields too.

Here the record is in First, Last, City, Age order, but the field list asks for City, Last, First.

let
    Source = Record.SelectFields([First="Ava",Last="Khan",City="Pune",Age=29],{"City","Last","First"})
in
    Source

Result: [City = "Pune", Last = "Khan", First = "Ava"]

Age is gone, and the remaining fields come back in the City, Last, First order you asked for.

Example 3: Handle a field that is not in the record

Naming a field the record does not have throws an error by default. Pass MissingField.UseNull to keep that field with a null value instead.

This record has Name and City, but you ask for Name and Country.

let
    Source = Record.SelectFields([Name="Ava",City="Pune"],{"Name","Country"},MissingField.UseNull)
in
    Source

Result: [Name = "Ava", Country = null]

Country does not exist in the source, so it comes back as null rather than erroring.

Drop the MissingField.UseNull argument and the same call raises Expression.Error: The field 'Country' of the record wasn't found. Use MissingField.Ignore instead and the missing Country is left out entirely, giving you just [Name = "Ava"].

Example 4: Apply it to every row of a table

Record.SelectFields works on a single record, so to use it across a table you run it per row with Table.TransformRows, then turn the records back into a table with Table.FromRecords.

Here is the starting data:

First	Last	City	Phone
Ava	Khan	Pune	x-401
Ben	Diaz	Reno	x-117
Cara	Okoye	Lagos	x-238

Keep only City and Last from each row, in that order:

let
    Source = Excel.CurrentWorkbook(){[Name="Contacts"]}[Content],
    Kept = Table.TransformRows(Source,each Record.SelectFields(_,{"City","Last"})),
    Back = Table.FromRecords(Kept)
in
    Back

The result keeps just the two columns, in the order listed:

City	Last
Pune	Khan
Reno	Diaz
Lagos	Okoye

Each row was a record, so Record.SelectFields subset and reordered it the same way it did the single records above. Table.TransformRows works much like List.Transform, running your function once per row.

Example 5: For a whole table, use Table.SelectColumns

If you are working on a full table rather than a single record, you do not need the per-row trick. Table.SelectColumns is the table-level equivalent. It keeps and reorders columns directly.

Here is the starting data:

OrderID	Item	Price	Notes
A-01	Fishing rod	100	ship
A-02	Reel	45	hold
A-03	Line spool	12	ship

Pick just the Item and Price columns:

let
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    Picked = Table.SelectColumns(Source,{"Item","Price"})
in
    Picked

The result has only those two columns:

Item	Price
Fishing rod	100
Reel	45
Line spool	12

This is the cleaner choice when your data is already a table. Reach for Record.SelectFields when you are inside a record, for example a single row pulled out with Table.TransformRows.

Things to keep in mind with Record.SelectFields

It both subsets and reorders. The output order follows your fields list, not the record’s original order, so listing names in a new order rearranges them (Example 2).
Field names are case-sensitive. "price" will not match a field called Price, and you get a missing-field error instead of the value.
A missing field throws by default. Naming a field the record does not have raises Expression.Error: The field 'X' of the record wasn't found. Pass MissingField.UseNull to get null for it, or MissingField.Ignore to drop it.
For a whole table, use Table.SelectColumns. It keeps and reorders columns directly, no per-row record handling needed.
Record.RemoveFields is the inverse. Name the fields you want gone instead of the fields you want to keep when most of the record should stay.
To pull a single value out, use Record.Field instead. Record.SelectFields always returns a record, even for one field, so use Record.Field when you want the bare value.

Common questions about Record.SelectFields

What is the difference between Record.SelectFields and Table.SelectColumns?

Record.SelectFields works on one record at a time, while Table.SelectColumns works on an entire table at once. Use the record version when you are inside a single row, and the table version for the whole dataset.

How is Record.SelectFields different from Record.RemoveFields?

They are opposites. Record.SelectFields keeps only the fields you name, while Record.RemoveFields keeps everything except the fields you name. Pick whichever needs the shorter list.

List of All Power Query Functions

Related Power Query Functions / Articles: