Table.First - Table Function

Table.First is a Power Query M function that returns the first row of a table or an optional default value if the table is empty. The function returns a single row of data.

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

Syntax

Table.First(
   table as table,
   optional default as any,
) as any

Description

The Table.First function allows you to retrieve the first row of a table and return it as a record. If the table is empty, you can provide a default value to return in its optional second argument.

Examples

Let’s look at an example of how to use the Table.First function. Here’s a simple table called Source:

Table.First dataset as start in Power Query M

To get the first row from this table, you use the Table.First function:

// Returns: [ Index = 1, Title = "1984", Genre = "Dystopian", TimesBorrowed = 30 ]
Table.First( Source )

What Happens Here? It returns a record containing all the column names and their values from that row. The function retrieves the first row of the Source table.

Table.First returns first row as a record in Power Query M

The result is a record that looks like the image above. You can achieve something similar by using:

Source{0}

This shorter expression also retrieves the first row from a table.

Handling an Empty Table

Now suppose we worked with an empty table. You can create one by using:

#table( type table , {} )

If we would apply the Table.First operation on an empty table, it will returns us a null value by default:

// Output: null
Table.First( #table( type table , {} ) )

Returning a Default Value

The Table.First function also allows you to return a default value if your input table is empty. For instance:

Table.First( 
  #table( type table, { } ) 
  "Test"
)

The above code returns the text value “Test” in case of a missing value. You can however also return other types of values like a record:

// Output: [ Index = 0, Title = "Missing", Genre = "Missing", TimesBorrowed = null ]
Table.First( 
  #table( type table, {} ),
  [ Index = 0, Title = "Missing", Genre = "Missing", TimesBorrowed = null ]
)

Complete M Code

To try this yourself you can use the following M code:

let
  Source = 
    #table(
      type table [ Index = Int64.Type, Title = text, Genre = text, TimesBorrowed = Int64.Type ],
      {
          { 1, "1984",                                  "Dystopian", 30 },
          { 2, "To Kill a Mockingbird",                 "Fiction",   50 },
          { 3, "The Great Gatsby",                      "Fiction",   45 },
          { 4, "Harry Potter and the Sorcerer's Stone", "Fantasy",   60 },
          { 5, "The Hobbit",                            "Fantasy",   35 },
          { 6, "Pride and Prejudice",                   "Romance",   40 },
          { 7, "The Catcher in the Rye",                "Fiction",   20 },
          { 8, "The Shining",                           "Horror",    25 }
      }
    ),
    TableFirst = Table.First( Source ),
    Source2 = #table( type table ,{  } ),
    InputEmpty = Table.First( Source2 ),
    DefaultValue = 
      Table.First( 
        #table( type table, {} ),
        [ Index = 0, Title = "Missing", Genre = "Missing", TimesBorrowed = null ]
      )
in
    DefaultValue

Other functions related to Table.First are:

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