Table.FromRecords Function in Power Query M (5 Examples)

Table.FromRecords builds a table from a list of records, where each record becomes a row and the field names become the columns. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you have a list of records and want to turn it into a proper table you can transform, this is the function you reach for.

Table of Contents

Syntax of Table.FromRecords Function

Table.FromRecords(records as list, optional columns as any, optional missingField as nullable number) as table

where

records (required, list). A list of records. Each record is one row, and its field names become the column names.
columns (optional, any). A list of column names, or a table type, that fixes which columns appear and in what order. Use it to reorder columns or drop ones you do not want.
missingField (optional, nullable number). Controls what happens when a record is missing a field that other records have. Options are MissingField.Error (the default), MissingField.UseNull, and MissingField.Ignore.

Returns: a table where each record in the list is a row and the field names become the columns. By default the fields of the first record decide the column set.

In plain terms, you hand it a list of records and it lays them out as a table.

Example 1: Build a table from a list of records

You have three records, each with a ProductID, a Product, and a Price. You want them as a table.

let
    Source = Table.FromRecords({[ProductID=101,Product="Notebook",Price=4.5],[ProductID=102,Product="Pen",Price=1.25],[ProductID=103,Product="Eraser",Price=0.75]})
in
    Source

Each record turns into a row, and the field names become the column headers.

You get this table back:

ProductID	Product	Price
101	Notebook	4.5
102	Pen	1.25
103	Eraser	0.75

Example 2: Fill a missing field with null

By default a record that is missing a field will error. Here the second record has no Dept, so pass MissingField.UseNull to fill the gap with null instead.

let
    Source = Table.FromRecords({[EmpID=1,Name="Asha",Dept="Sales"],[EmpID=2,Name="Marco"],[EmpID=3,Name="Lena",Dept="Finance"]},{"EmpID","Name","Dept"},MissingField.UseNull)
in
    Source

The Dept for Marco becomes null rather than stopping the query.

Here is the result:

EmpID	Name	Dept
1	Asha	Sales
2	Marco
3	Lena	Finance

Example 3: Reorder and drop columns

Each record has City, Country, and Code. You want only Code and City, with Code first. Pass a list of column names as the second argument.

let
    Source = Table.FromRecords({[City="Berlin",Country="Germany",Code="BER"],[City="Lyon",Country="France",Code="LYS"],[City="Porto",Country="Portugal",Code="OPO"]},{"Code","City"})
in
    Source

Only the listed columns appear, in the order you list them, so Country is dropped.

Here is the result:

Code	City
BER	Berlin
LYS	Lyon
OPO	Porto

Example 4: Set column types with a table type

Instead of a plain name list, you can pass a table type to fix both the columns and their data types. Records that are missing a field still need MissingField.UseNull.

let
    Source = Table.FromRecords({[CustomerID=1,FirstName="Bob",MiddleInitial="C",LastName="Smith"],[CustomerID=2,FirstName="Sarah",LastName="Jones"],[CustomerID=3,FirstName="Harry",MiddleInitial="H"]},type table[FirstName=nullable text,MiddleInitial=nullable text,LastName=nullable text],MissingField.UseNull)
in
    Source

The table keeps only the three typed columns, and the missing names come through as null.

You get this back:

FirstName	MiddleInitial	LastName
Bob	C	Smith
Sarah		Jones
Harry	H

Example 5: Create a small lookup table inline

When you need a tiny reference table inside a query, you can write it straight into the M with Table.FromRecords instead of pulling it from a worksheet.

let
    StatusLookup = Table.FromRecords({[Code="A",Status="Active"],[Code="P",Status="Pending"],[Code="C",Status="Closed"]}),
    Source = StatusLookup
in
    Source

You now have a StatusLookup table you can merge against the rest of your query.

Here is the result:

Code	Status
A	Active
P	Pending
C	Closed

Things to keep in mind with Table.FromRecords

Field names are case-sensitive. Price and price are treated as two different columns, which can split your data unexpectedly.
Values keep their types. Numbers stay numbers and text stays text, so you do not need to convert them after building the table.
For a list of lists, use Table.FromRows instead. Table.FromRecords expects records with named fields. If your rows are plain lists of values, reach for Table.FromRows.
Fill missing fields with MissingField.UseNull. If a later record lacks a field, pass MissingField.UseNull to get null instead of an error, then remove null values in Power Query if you want them gone.

Common questions about Table.FromRecords

What is the difference between Table.FromRecords, Table.FromRows, and #table?

Table.FromRecords takes records with named fields. Table.FromRows takes plain lists of values and you supply the column names separately. #table is the lowest-level constructor, where you pass column names and rows directly.

List of All Power Query Functions

Related Power Query Functions / Articles: