Type.TableRow - Type Function

Type.TableRow is a Power Query M function that retrieves the row type of a specified table type. The function returns a record type representing the row type of the input table type.

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

Syntax

Type.TableRow( table as type ) as type

Description

The Type.TableRow function returns the row type of the specified table type. The result will always be a record type.

Examples

Let’s look at an example of the Type.TableRow function.

Extracting Row Types from Table Types

Let’s start with extracting the row type from a table type. The row type is essentially the structure of each row, represented as a record. To do this, you can use the Type.TableRow function.

Extracting the Row Type from a Simple Table Type

In this example, we have a simple table type with two fields: myDate (a date) and Amount (a number). By applying Type.TableRow, we get the row type as a record:

// Output: type [ myDate = date, Amount = number ]
Type.TableRow( type table [ myDate = date, Amount = number ] )

This gives us the structure of each row as a record, showing the field names and their types.

Extracting Detailed Field Information

If you want more details about the fields in the row type, such as whether they are optional or required, you can use the Type.RecordFields function. This will return the field names, their data types, and additional properties like Optional.

Here’s how to extract detailed information about the fields in a table:

/* Output:
type [ 
       myDate  = [ Type = type date, Optional = false ], 
       Amount  = [ Type = type number, Optional = false ] 
     ]
*/
let
  myType = type table [ myDate = date, Amount = number ],
  tableRowType = Type.TableRow( myType ),
  RecordFields = Type.RecordFields( tableRowType )
in
  RecordFields

Extracting the Type from a Table

If you are working with an actual table (not just a table type), you can extract its type using the Value.Type function. This gives you the table type, which can then be used with Type.TableRow to retrieve the row structure.

Let’s say you have a table with one row. You can extract the type of the table and then get the row type:

// Output: type [ myDate = date, Amount = number ]
let
  myTable = #table( 
    type table[ myDate = date, Amount = number] , 
    { { #date( 2024,1,1 ), 100 } } 
  )
  myTableType = Value.Type( myTable ), 
  tableRowType = Type.TableRow( myTableType ) 
in
    tableRowType

This extracts the table type and then retrieves the row type, which tells you the structure of each row in the table.

Combining Table Types

In some cases, you may need to combine two different table types. Your first instinct might be to use the Table.Combine function, but it doesn’t work for combining table types. Instead, you can manually combine the row types by extracting their fields and merging them.

Steps for Combining Table Types:

Extract the row type from each table type using Type.TableRow.
Extract the fields from each row type using Type.RecordFields.
Combine the fields using Record.Combine.
Convert the combined record back into a table type using Type.ForRecord.

Let’s combine two table types—one with a text field A and the other with a numeric field B:

let 
  type1 = type table [ A = text ], 
  type2 = type table [ B = number ],
  RecordTypes = List.Transform( 
    { type1, type2 }, 
    each Type.RecordFields( Type.TableRow( _ ) ) 
  ),
  CombineRecords = Record.Combine( RecordTypes ),
  TableType = type table Type.ForRecord( CombineRecords, false ),
  InspectTableType = Type.TableSchema( TableType )
in
    InspectTableType

In this example, we:

Extract the row types from two table types.
Extract the field details from both row types.
Combine the fields using Record.Combine.
Convert the combined record back into a table type and inspect the schema.

Other functions related to Type.TableRow are:

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