Table.RowCount

What is Power Query M Language?

Power Query is a data transformation and analysis tool that is a part of Microsoft Excel and Power BI. The M language is the programming language used by Power Query to perform data transformation and analysis. It is a functional programming language that is designed to work with data.

The M language is used to perform various tasks in Power Query, such as data cleaning, data transformation, and data aggregation. It is a powerful language that can handle large amounts of data and complex data transformations.

What is Table.RowCount Function?

The Table.RowCount function is used to count the number of rows in a table in Power Query. It returns the number of rows in the table as an integer value.

The syntax of the Table.RowCount function is as follows:

The table argument is the table for which you want to count the number of rows. The optional countNulls argument specifies whether to count null values as part of the row count. If countNulls is set to true, then null values are counted as part of the row count. If countNulls is set to false or not specified, then null values are not counted as part of the row count.

How Table.RowCount Function Works?

The Table.RowCount function works by counting the number of rows in the table. It first checks whether the table argument is a table type. If the table argument is not a table type, then an error is returned.

If the table argument is a table type, then the function checks whether the optional countNulls argument is specified. If the countNulls argument is specified and set to true, then the function counts the null values as part of the row count. If countNulls is not specified or set to false, then null values are not counted as part of the row count.

The function then counts the number of rows in the table and returns the row count as an integer value.

Examples of Table.RowCount Function

Let’s take a look at some examples of the Table.RowCount function in Power Query.

Example 1: Counting Rows in a Table

Suppose we have a table named Sales that contains the sales data for a company. We want to count the number of rows in the Sales table using the Table.RowCount function. The M code for this task is as follows:

The above M code first gets the Sales table from the current workbook and assigns it to the variable Source. It then calls the Table.RowCount function on the Source variable and assigns the row count to the variable rowCount. Finally, it returns the value of the rowCount variable.

Example 2: Counting Rows with Null Values

Suppose we have a table named Customers that contains the customer data for a company. The Customers table contains some null values in the rows. We want to count the number of rows in the Customers table, including the null values, using the Table.RowCount function. The M code for this task is as follows:

The above M code first gets the Customers table from the current workbook and assigns it to the variable Source. It then calls the Table.RowCount function on the Source variable and specifies the countNulls argument as true to include the null values in the row count. It assigns the row count to the variable rowCount and returns the value of the rowCount variable.

The Table.RowCount function is a powerful function in Power Query that helps in counting the number of rows in a table. It is a simple function that is easy to use and provides accurate results. In this article, we explored the M code behind the Table.RowCount function and learned how it works. We also looked at some examples of using the Table.RowCount function in Power Query.

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

Upcoming Courses

Contact Us