Table.Last - Table Function

Table.Last is a Power Query M function that extracts the last row of a table or an optional default value if the table is empty. The function returns the last row or the default value.

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

Syntax

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

Description

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

Examples

Let’s look at an example of how to use the Table.Last function.

Let’s start with a simple table called Source:

To get the last row from this table, you use the Table.Last function:

// Returns: [ Index = 8, Title = "The Shining", Genre = "Horror", TimesBorrowed = 25 ]
Table.Last( Source )

What Happens Here? The function returns a record containing the last row’s column names and values.

Handling an Empty Table

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

#table( type table , {} )

If we apply the Table.Last operation on an empty table, it will return a null value by default:

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

Returning a Default Value

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

Table.Last( 
  #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.Last( 
  #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 }
      }
    ),
    TableLast = Table.Last( Source ),
    Source2 = #table( type table, {} ),
    InputEmpty = Table.Last( Source2 ),
    DefaultValue = 
      Table.Last( 
        #table( type table, {} ),
        [ Index = 0, Title = "Missing", Genre = "Missing", TimesBorrowed = null ]
      )
in
    DefaultValue

Other functions related to Table.Last are:

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