Have you ever needed to count the rows in a data set?. There are several methods you can use with Power Query. This blog post demonstrates three ways to check how many rows Excel imports into Power Query. I will show you why this is helpful in Power Query. So, your solution is here, whether you’re just getting started with Power Query or looking for a suggested way to count your data rows.
Why Count Rows With Power Query?
If you are a Power Query user, you already know the count of row display truncates at 999+ rows. So, it is possible to scroll down to the bottom of your data set until you reach the last rows of data. But, if you are using Power Query, you are probably dealing with many data rows. So, that is time-consuming, and there are alternative ways to count your data rows. I often check how many rows have been imported just as a sense check for my peace of mind. I think it’s good practice to ensure you are working with your complete data set. So, this article will demonstrate three ways to count data rows in Power Query.
Import A File Into Power Query.
Let’s walk through an example to demonstrate how easy it is to check how many rows Excel imports into Power Query. But first, let’s import a sample data set into the Power Query editor. I have downloaded a free sample data set from the New York City Taxi and Limousine Commission. This data is free. You can find datasets covering pick-up/drop-off times and locations, trip distances, fares, rate and payment types, passenger counts, etc. So I am working with the January 2022 set. If you want to dive into NYC Taxi data, the link is here. Get lost in all that traffic data!
So, first lets iSo, first, let’s import the CSV file of data into Excel Power Query. Follow the steps below. My data set is currently on my desktop. I am using Excel 365 subscription service.
- Open a new Excel workbook.
- Data Tab | Get &Transform group
- Get Data | From File
- From Text/CSV
- Navigate to your File
- Select import
II do not want to load the data at this point, so I select Transform. At this stage, Excel opens up the Power Query Editor. Power query has already imported the data, used the first row as headers and automatically selected the data type for the columns. The corresponding steps of the query are located in the Query Settings window.
So, now we have a date set, let’s look at counting how many rows of data have been imported.
Use The Count Rows Option In The Transform Tab. Method 1.
The first method is very simple. In the Power Query Editor, follow the steps below.
- Transform Tab
- Table group
- Count Rows
The number of rows in your imported table will be displayed. But wait!. Where has the data gone!. Do not panic. The data is still there. Power query has added an extra step into the query. To undo any of the steps in the query, hit the x by the side of the step. This will return the Power Query Editor screen back to displaying the imported data lines. In this example to return to the original data screen select the x next to the Counted Rows step. This is a great way to count the table rows maybe as a once off method, but switching between the data display and the row count can be annoying. If this is the case for your one of the next two methods maybe more applicable.
The TableRow.Count Method. Method 2.
So, if the first method of counting rows is not convenient, you can have a separate permanent count of rows in the power query editor. This will appear as a query by itself, and you can refer to the count of table rows whenever it is needed. This method uses the TableRow.Count method.
This method uses the Table.RowCount function. The syntax of this function is
Table.RowCount(table as table) as number
It basically returns the number of rows in your table.
Follow the steps below to write the M code to count your table rows.
- Home Tab
- New Source | Other Sources | Blank Query
- Type your query. In my example, it is = Table.RowCount(#”yellow_tripdata_2022-01″)
- I have renamed this query to RowCount to always have that metric available
This is basically the same query as the first method, but typed directly into a blank query. Now I have a separate query, which can be seen in the query window.
Using The List Function. Method 3.
The last way to count how many rows are in our data table or set is using the List Function. Just like Mthod 2 in this article, a new query can be created to use the List Function. The List Function in Power Query returns the number of items in a list. So, if we take one column from our table, we can count how many records are in the list.
So, this method uses the List.Count function. The syntax of this function is
List.Count(list as list) as number
It basically returns the number of rows in your table. A simple method. Again, create a new query in POwer Query
- Home Tab
- New Source | Other Sources | Blank Query
- Type your query. In my example, it is = List.Count(#”yellow_tripdata_2022-01″[VendorID])
The list is taken from the original data table, using the specific title of one of the columns to count the rows of data. In this example, I use the count of the VendorID. Again Power Query returns the correct number of rows of data. If the original column you use in the query is deleted from the data table, then Power Query returns an error. The column used in the function must be identical in name and available.
Thank you for following along with this tutorial on how to count rows of data in Power Query. We hope you found it helpful! In the next installment, we will be covering more ways to analyze your data using Power Query and Power BI. If you want to be notified when that article is published, sign up for our email list, and we’ll send you an update as soon as it’s available. Thanks again for reading!
More Excel Power Query Articles.
Use the link below to join my mailing list.. No Spam. Just Excel Power Query, Pivot and Power BI stuff. The occasional discount offer too.