Table.SingleRow

D

T

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

In this article, we will explore the M code behind the Table.SingleRow function and how it can be used to enhance your data transformation and cleansing efforts.

What is the Table.SingleRow Function?

The Table.SingleRow function is a Power Query M function that returns a table containing a single row from the input table. It is often used in conjunction with other functions in Power Query to extract specific data from a table.

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


Table.SingleRow(table as table)


Where `table` is the input table from which you want to extract a single row.

Understanding the M Code Behind Table.SingleRow

The M code behind the Table.SingleRow function is relatively simple. When you apply the function to a table, Power Query creates a new table that contains only the first row of the original table.

Here is the M code behind the Table.SingleRow function:


let

Source = table,

FirstRow = Table.FirstN(Source, 1),

Output = FirstRow{0}

in

Output


The code starts by setting the input table as the `Source` variable. Next, the `FirstRow` variable is created by using the Table.FirstN function to extract the first row of the `Source` table. Finally, the `Output` variable is created by accessing the first row of the `FirstRow` table using the {0} syntax.

Using Table.SingleRow in Practice

Table.SingleRow can be incredibly useful when working with large datasets. For example, let’s say you have a table that contains sales data for a particular product, and you want to extract the data for a single day. You can use the Table.SingleRow function to extract the row that corresponds to that day.

Here is an example M code for using Table.SingleRow to extract data for a single day:


let

Source = sales_table,

SingleDay = Table.SingleRow(Table.SelectRows(Source, each [Date] = #date(2021, 10, 1)))

in

SingleDay


In this example, the `Source` variable is set to the original sales table. The `SingleDay` variable is created by using the Table.SelectRows function to filter the `Source` table to only include rows where the `Date` column is equal to October 1, 2021, and then applying Table.SingleRow to extract the single row that matches that date.

The Table.SingleRow function is a powerful tool in Power Query that allows you to extract specific data from a table. By understanding the M code behind the function, you can use it to enhance your data transformation and cleansing efforts. Whether you’re working with large datasets or just need to extract a single row of data, Table.SingleRow is a useful function to have in your Power Query toolbox.

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)