How to Count Rows in Power Bi Table?

Summary: Counting rows is one of the fundamental task in Power BI. This blog takes you through different methods that unfold various methods to find the total number of rows.

Introduction

Accurate data representation is crucial in business. Amongst the different tools that are available Power BI emerges as a powerful analytical tool. With the accurate data representation, it helps in precise data representation that is comprehensible to all. One of the common tasks that users face is determining the total number of rows in a table. 

In this blog, we will be covering the key details of how to count rows in Power BI. We will be highlighting the different methods that one can use to check number of rows in power query and how to display total number of records in power bi

Understanding Power BI and Its Importance

Power BI is a suite of business analytics tools that allows users to analyze data and share insights. It provides interactive visualizations and business intelligence capabilities with a simple interface for end users to create their own reports and dashboards.

The ability to count rows in a table is crucial for data analysis, as it helps users understand the size of their datasets, identify trends, and make informed decisions based on the data. 

Whether you are working with sales data, customer information, or any other dataset, knowing how to count rows effectively in Power BI can enhance your reporting and analytics capabilities.

Read More: Power BI Tutorial: A Complete Guide

How to Count Rows in Power Query

This method is for when you want the output of your query to be the actual row count.

Method A: Using the “Count Rows” Transformation

This is the simplest method and replaces your table with a single numerical value representing the row count.

1. With your table open in the Power Query Editor, navigate to the Transform tab.
2. In the Table group, click on Count Rows.
3. The table will be replaced by a single number, which is the total count of rows.

Method B: Using the Table.RowCount Function

This M language function provides more flexibility and allows you to use the row count in subsequent steps or a separate query.

1. To create a new query that only contains the row count:
   • In the Power Query Editor, go to the Home tab.
   • Click New Source > Other Sources > Blank Query.
   • In the formula bar, type = Table.RowCount(YourTableName), replacing YourTableName with the name of the query you want to count.
2. To add the row count as a step in an existing query:
   • Select the query you are working on.
   • Click the “fx” icon in the formula bar to add a new custom step.
   • Enter = Table.RowCount(NameOfPreviousStep), replacing NameOfPreviousStep with the name of the prior step (e.g., “Changed Type”).

How to Check the Number of Rows in Power Query

Use these methods when you just need to see the number of rows for informational purposes without altering your data.

Method A: Using the Status Bar

For a quick look, the bottom of the Power Query Editor provides a brief summary of your data.

1. Open your query in the Power Query Editor.
2. Look at the status bar in the bottom-left corner. It will display the number of columns and the number of rows loaded in the preview (e.g., “13 columns, 91 rows”).
3. Note: For large datasets, this count may be truncated and display “999+ rows”.

Method B: Using Column Profiling

To get a more accurate count for larger datasets directly in the editor view:

1. In the Power Query Editor, go to the View tab.
2. In the Data Preview section, check the box for Column profiling.
3. At the bottom of the editor, click on the text that says “Column profiling based on top 1000 rows”.
4. Select “Column profiling based on entire data set”.
5. The row count displayed in the status bar and column statistics will now reflect the entire dataset. Be aware this can slow down performance on very large tables.

How to Display the Total Number of Records in Power BI

After you have loaded your data from Power Query into Power BI, you can display the total row count in your reports and dashboards. The most common way is to use a Card visual with a DAX measure.

Step 1: Create a DAX Measure for the Row Count

1. In Power BI Desktop, go to the Report view.
2. From the Home or Modeling tab, click on New Measure.
3. In the formula bar that appears, enter the following DAX formula. Replace ‘YourTableName’ with the actual name of your table:
   Total Rows = COUNTROWS(‘YourTableName’)
4. Press Enter or click the checkmark to save the measure.

Step 2: Display the Count in a Card Visual

1. In the Visualizations pane, click on the Card visual icon to add it to your report canvas.
2. With the new Card visual selected, go to the Data pane.
3. Find the Total Rows measure you just created and drag it into the Fields well of the Card visual.
4. The card will now display the total number of records from your table.

How To Get Total Number of Rows in Power Query

To find the total number of rows in Power Query, the simplest method is on the Transform tab—just click Count Rows. This replaces your table with the total count. For more flexibility, you can use the M language function Table. RowCount(YourTable) in a new step or a separate query.

Method 1: Using the “Count Rows” Transformation (Easiest)

This is the most straightforward method. It replaces your entire table with a single value: the total row count. This is useful when the count itself is the final result you need.

1. Open your table in the Power Query Editor.
2. Go to the “Transform” tab in the ribbon at the top.
3. In the “Table” section, click the “Count Rows” icon.
   • Result: Your data table will be replaced by a single number representing the total count of rows.

Method 2: Using the Table.RowCount Function (Most Flexible)

This method uses a Power Query M language function. It’s ideal when you need to use the row count in another calculation or want to create a separate query that just shows the count without changing your original table.

1. Open the Power Query Editor.
2. Make sure the Formula Bar is visible. If not, go to the “View” tab and check the box for “Formula Bar”.
3. Decide where you want the count:

Option A: As a new step in your current query:

• Select the last step in your “Applied Steps” pane on the right.
• Click the “fx” icon next to the formula bar to create a new custom step.
• Type = Table.RowCount(NameOfPreviousStep) in the formula bar, replacing NameOfPreviousStep with the actual name of the previous step (e.g., “Changed Type”).
• Press Enter.

Option B: As a completely new query:

• In the “Home” tab, click “New Source” > “Other Sources” > “Blank Query”.
• A new query will be created. In its formula bar, type = Table.RowCount(YourTableName), replacing YourTableName with the name of the table you want to count.
• Press Enter.

Conclusion

Whether you need a quick check in Power Query using “Count Rows” or a dynamic display in a Power BI report with a COUNTROWS DAX measure, both tools provide simple and effective methods. Choosing the right technique depends on whether you are preparing data or visualizing the final result.

Frequently Asked Questions

How can I display the total number of sales transactions on a dashboard card?

To show a live count, create a DAX measure using the formula: Total Transactions = COUNTROWS(‘SalesTable’). Then, add a “Card” visual to your report and drag this new measure into it. The card will now display the total number of rows, updating automatically with your data.

How do I quickly verify the row count in Power Query before loading a large file?

In the Power Query Editor, select your query. Go to the “Transform” tab and click “Count Rows.” This action replaces the data preview with a single number representing the total row count. You can then remove this step from “Applied Steps” before closing and applying the changes.

I need to calculate a “percentage of total” in a DAX formula. How do I get the total row count for the calculation?

First, create a base measure like Total Records = COUNTROWS(‘YourTable’). You can then use this measure as the denominator in other DAX calculations. For example, DIVIDE([Category Count], [Total Records]) will give you the percentage of total, ensuring your calculations are clean and efficient.