Table.FirstValue

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Table.FirstValue

One of the most commonly used M functions in Power Query is the Table.FirstValue function. This function is used to return the first value of a column in a table. In this article, we will discuss the M code behind the Table.FirstValue function and how it can be used in Power Query.

Syntax

The syntax for the Table.FirstValue function is as follows:

Table.FirstValue(table as table, column as text, optional defaultValue as any) as any

– `table`: The table containing the column to be evaluated.

– `column`: The name of the column to be evaluated.

– `defaultValue`: An optional value to be returned if the column is empty.

Example

Let’s take a look at an example of how the Table.FirstValue function can be used in Power Query:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcisqzcnJSijKzUwMUbJwMlCKjW0MlFV4A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),

FirstValue = Table.FirstValue(Source, "Column1")

in

FirstValue

In this example, we have a table named “Source” with one column named “Column1”. We want to return the first value of the “Column1” column. We use the Table.FirstValue function to achieve this.

How it Works

The Table.FirstValue function works by returning the first non-null value in the specified column of the specified table. If the column is empty and a default value is not specified, the function will return null.

Let’s take a closer look at the M code behind the Table.FirstValue function:

(Table.FirstN(table[column], 1){0}?)?

This code breaks down as follows:

– `Table.FirstN(table[column], 1)`: This expression returns the first row of the specified column in the specified table.

– `{0}`: This index is used to return the first (and only) row of the result as a record.

– `?`: This optional operator is used to handle null values. If the first row is null, the result will be null.

The Table.FirstValue function is a powerful function in Power Query that is used to return the first non-null value in a specified column of a specified table. By understanding the M code behind this function, you can gain a better understanding of how Power Query works and how you can use it to manipulate and transform your data.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)