Pinterest Pixel

Count Rows Using Power Query or Get & Transform

Bryan
Power Query or Get & Transform (In Excel 2016) lets you perform a series of steps to transform your Excel data.
 One of the steps it allows you to take is to count the number of rows in your query. And with just one step, you can get the count very easily!

Power Query or Get & Transform (In Excel 2016) lets you perform a series of steps to transform your Excel data.  One of the steps it allows you to take is to count the number of rows in your query. And with just one step, you can get the count very easily!

Key Takeaways

  • Power Query Provides a Simple Way to Count Rows: You can easily count the number of rows in your data set using Power Query’s built-in features, such as by applying a step to calculate the row count in your query.
  • Using the “Table.RowCount” Function: To count rows in Power Query, you can use the Table.RowCount function. This function counts the rows in a given table and returns the total number of rows as a single value.
  • Counting Rows in the Query Editor: After loading data into Power Query, open the Query Editor, and in the formula bar, you can write a simple query like = Table.RowCount(YourTableName) to get the row count.
  • Row Counting with Filters: Power Query allows you to count rows after applying filters or transformations. By applying a filter to your data, the row count will automatically update to reflect the filtered dataset.
  • Customizing Row Counting: You can count rows dynamically as part of your query steps, making it easier to incorporate the row count into your data analysis workflow, such as displaying it as a summary or for validation purposes.

How to Count Rows Using Power Query

STEP 1: Select your data and turn it into an Excel Table by pressing the shortcut Ctrl + T or by going to Insert > Table

Count Rows Using Power Query or Get & Transform

 

STEP 2: Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:

 Keep Duplicates Using Power Query or Get & Transform

Excel 2013 & 2010:

Keep Duplicates Using Power Query or Get & Transform

 

STEP 3: This will open up the Power Query Editor.

Go to Transform> Table > Count Rows

Count Rows Using Power Query or Get & Transform

 

STEP 4: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the row count!

Count Rows Using Power Query or Get & Transform

You now have your new table with the row count of 10!

Count Rows Using Power Query or Get & Transform

Troubleshooting Common Issues

Dealing with Null Values and Errors

When you venture into the realm of data analysis with Power Query, you might stumble upon tricky hurdles such as null values and errors lurking within your datasets. Null values can throw a wrench into your calculations, as standard COUNT functions don’t differentiate between actual data and these blanks. To maintain accuracy, filter out null values before starting your count. Or, you could utilize the COUNTROWS function that better handles such instances.

Watch out for formula errors too. A wrong click or a typo while crafting your DAX formulas could lead to headaches. Double-check that table names and syntax are precise. Remember, even a small mistake can lead to a big discrepancy in your results.

Performance Considerations When Counting Large Datasets

Counting rows in extensive datasets in Power Query can be akin to sprinting a marathon—it can exhaust your system’s resources if not planned properly. Before setting off on this data journey, consider the toll it takes on performance. For more complex datasets, refrain from counting directly; instead try summarized data models or aggregated data to keep things running smoothly.

When testing your counting methods, start small. A sample dataset gives you a condensed playground to pinpoint inefficiencies or errors that could magnify in larger datasets. And remember, Power BI offers various DAX measures like COUNTROWS to maximize efficiency—utilize these for dynamic counting that won’t bog down performance.

Frequently Asked Questions

How do I count the number of rows in Power Query?
To count rows in Power Query, use the Table.RowCount function. In the Power Query Editor, you can add a custom step by typing = Table.RowCount(YourTableName) in the formula bar to get the row count of your data.

Can I count rows after applying filters in Power Query?
Yes, you can count rows after applying filters. Power Query will automatically update the row count to reflect the filtered data. Once a filter is applied, you can use Table.RowCount to count the rows in the filtered table.

Where do I find the row count in Power Query?
The row count is typically displayed in the Power Query Editor, but if you want to extract it, you can create a custom column or use a summary table. You can also apply the Table.RowCount function and display the result as part of your query steps.

Can I use Power Query to count rows for specific conditions?
Yes, you can count rows based on specific conditions by applying filters before using the Table.RowCount function. For example, you can filter rows based on a column value, and then count the remaining rows using the Table.RowCount function.

Is there a way to count rows dynamically in Power Query?
Yes, you can count rows dynamically by using the Table.RowCount function after applying transformations or filters. This ensures that the row count will update automatically as your query changes. You can also use it as a reference in a summary or validation table.

If you like this Excel tip, please share it



Count Rows Using Power Query or Get & Transform | MyExcelOnline


Bryan

Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).

He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.

See also  How To Install Power Query in Excel 2010

Star 30 Days - Full Access Star

One Dollar Trial

$1 Trial for 30 days!

Access for $1

Cancel Anytime

One Dollar Trial
  • Get FULL ACCESS to all our Excel & Office courses, bonuses, and support for just USD $1 today! Enjoy 30 days of learning and expert help.
  • You can CANCEL ANYTIME — no strings attached! Even if it’s on day 29, you won’t be charged again.
  • You'll get to keep all our downloadable Excel E-Books, Workbooks, Templates, and Cheat Sheets - yours to enjoy FOREVER!
  • Practice Workbooks
  • Certificates of Completion
  • 5 Amazing Bonuses
Satisfaction Guaranteed
Accepted paymend methods
Secure checkout

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...