Table.FromRecords - Table Function

Table.FromRecords is a Power Query M function that converts a list of records into a table. The function returns a table composed of the input records.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

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

Argument	Attribute	Description
records
columns	optional
missingField	optional	The MissingField.Type determines the function’s reaction to operations on missing columns. When omitted, it uses MissingField.Error and generates an error for missing columns. Alternatives include MissingField.UseNull, substituting null for missing columns, and MissingField.Ignore, which ignores missing columns.

Description

Table.FromRecords transforms a list of records into a table, where each record becomes a table row.

Examples

In its simplest form, Table.FromRecords creates a table by taking a list of records. Each record in the list corresponds to a row in the resulting table.

Table.FromRecords( 
   {  
      [ ProductKey = 1, Product = "Apple" ],  // first record
      [ ProductKey = 2, Product = "Prume" ]   // second record
   } 
)

This code generates a table with two rows and two columns, “ProductKey” and “Product.” The function infers the column names from the keys in the records, and omits the data types. The resulting table looks like this:

Table.FromRecords creates a table by providing records in Power Query M

Specifying Column Types

You can also specify the data types for each column by providing a custom table type. Here’s how:

Table.FromRecords( 
   {  
      [ ProductKey = 1, Product = "Apple" ], 
      [ ProductKey = 2, Product = "Prume" ]
   }, 
   type table[ProductKey = Int64.Type, Product = Text.Type ] 
)

In this example, the ProductKey column is explicitly defined as an integer (Int64.Type), and the Product column is defined as text (Text.Type). The resulting table will show these data types:

Table.FromRecords can specify columns and data types in Power Query M

Handling Column Order

The order of fields in the records does not affect the order of columns in the table. The columns are organized based on the order of the specified column in the provided table type:

Table.FromRecords( 
   {  
      [ Product = "Apple", ProductKey = 1 ], 
      [ ProductKey = 2, Product = "Prume" ]
   }, 
   type table[ProductKey = Int64.Type, Product = Text.Type ] 
)

Despite the “Product” field appearing first in one of the records, the resulting table organizes the columns as per the defined table type:

Table.FromRecords can specify columns and data types in Power Query M 1

Handling Missing Fields

One challenge when working with tables is dealing with missing data. By default, Table.FromRecords will throw an error if a field is missing in any of the records.

Table.FromRecords(
  {
    [ProductKey = 1, Product = "Apple"], 
    [ProductKey = 2, Product = "Prume"], 
    [ProductKey = 3] // This record misses Product
  }
)

In this example, the third record lacks the “Product” field, which results in an error when the table is created.

Table.FromRecords returns an error for missing fields in Power Query M

This error occurs because Table.FromRecords uses the enumeration MissingField.Error by default, meaning it expects all fields to be present in every record.

Using MissingField.UseNull

To handle missing fields without error, you can instruct the function to insert null in place of missing values using the MissingField.UseNull option.

Table.FromRecords( 
   {  
      [ ProductKey = 1, Product = "Apple" ], 
      [ ProductKey = 2, Product = "Prume" ], 
      [ ProductKey = 3 ] // Missing Product field
   }, 
   null, 
   MissingField.UseNull  // returns null for missing fields
)

This code creates the same table as before, but instead of throwing an error, it fills the missing “Product” field with null. The resulting table is:

Table.FromRecords returns null values with MissingField.UseNull in Power Query M

Alternatively you could have used MissingField.Ignore.

Try it Yourself

You can paste the following code into the advanced editor to experiment with these examples:

let
  RecordsOnly = 
    Table.FromRecords( 
      {  
        [ ProductKey = 1, Product = "Apple" ],  // first record
        [ ProductKey = 2, Product = "Prume" ]   // second record
      } 
    ),
  RecordAndType = 
    Table.FromRecords( 
      {  
        [ ProductKey = 1, Product = "Apple" ], 
        [ ProductKey = 2, Product = "Prume" ]
      }, 
      type table[ProductKey = Int64.Type, Product = Text.Type ] 
    ), 
  ColumnOrder = 
    Table.FromRecords( 
      {  
        [ Product = "Apple", ProductKey = 1 ], 
        [ ProductKey = 2, Product = "Prume" ]
      }, 
      type table[ProductKey = Int64.Type, Product = Text.Type ] 
    ),
  MissingValue = 
    Table.FromRecords(
      {
        [ ProductKey = 1, Product = "Apple" ], 
        [ ProductKey = 2, Product = "Prume" ], 
        [ ProductKey = 3 ] // This record misses Product
      }
    ),
  MissingValue_UseNull = 
    Table.FromRecords( 
      {  
        [ ProductKey = 1, Product = "Apple" ], 
        [ ProductKey = 2, Product = "Prume" ], 
        [ ProductKey = 3 ] // Missing Product field
      }, 
      null, 
      MissingField.UseNull  // returns null for missing fields
    )
in
  MissingValue_UseNull

Learn more about Table.FromRecords in the following articles:

Create Tables from Scratch in Power Query M (40+ Examples)
Creating tables from scratch in Power Query can be tricky, but this post shows you how. You learn how to work with lists, records and much more! » Read more

Other functions related to Table.FromRecords are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-fromrecords