Best Way to Count Rows and Distinct Values in Power BI Power Query

Last week, one of my subscribers asked me a question about Power Query in Power BI. He was working with a Product table that has three columns: Product Key, Date, and Sold. For one Product Key, there are many records with different dates.

The requirement was simple. He wanted to count how many times the value “YES” appears in the Sold column for each Product Key.

To do this, Power Query provides a Group By option that lets us count rows and perform a distinct count with just a few clicks.

In this tutorial, I will show you how to add a column to count rows and how to add a column for distinct count using Power Query. You will see that this can be done in just four clicks by using the Group By feature in Power BI Power Query.

Count Rows and Distinct Values in Power BI Power Query

For this example, I am using the Financials table that comes with Power BI. In the screenshot below, you can see the sample data loaded into Power BI.

Add a Count Rows Column in Power BI Power Query

In this section, we will use the Group By option in Power Query to add a new column that shows the total number of rows for each group.

To do this, follow the steps below:

1. In Power BI Desktop, under the Home tab, click on Transform data to open the Power Query Editor.

2. Once the Power Query Editor opens, select the column you want to group by. In this example, select the Product column.

3. Right-click on the selected column (Product) and select Group By from the menu.

4. This will open the Group By window. Keep the Basic option selected, choose Product as the Group by column, enter Count as the new column name, select Count Rows as the operation, and then click OK.

5. After clicking OK, Power Query will create a new table that shows each Product and the total number of rows for that Product.

This way, you can add a count rows column in Power BI Power Query.

Add a Distinct Count Rows Column in Power BI Power Query

In some scenarios, you may want to count only unique values instead of counting all rows. For this, Power Query provides the Distinct Count option in the Group By feature.

To add a Distinct Count column, follow the steps below:

1. In Power Query Editor, select the column you want to group by. For example, select the Country column.

2. Right-click on the selected column and choose Group By.

3. In the Group By window, keep the Basic option selected. Set Group by to Product. In the New column name, enter Distinct Count. In the Operation dropdown, select Count Distinct Rows. Click OK.

4. Power Query will now create a new column that shows the distinct count for each product.

You can add a distinct count rows column in Power BI Power Query.

Count a Specific Value in Power BI Power Query

For this example, we are using the table shown below. The table contains Product Key, Date, and Sold columns.

Now, the requirement is to count how many times a specific value (YES) appears in the Sold column for each Product Key.

To achieve this in Power Query, follow the steps below:

1. In Power BI Desktop, go to the Home tab and click Transform data to open the Power Query Editor.
2. In Power Query Editor, select the Sold column.

3. Click the filter icon and select only the value YES.

4. Now select the Product Key column. Right-click on the column and choose Group By.

5. In the Group By window, keep the Basic option selected. Set Group by to Product Key. In the New column name, enter YES Count. From the Operation dropdown, select Count Rows, and then click OK.

6. After clicking OK, Power Query will create a new table that shows each Product Key and the total count of YES values.

In this article, I explained how to use Power Query in Power BI to count rows, count distinct values, and count a specific value like YES using the Group By feature. I showed how to group data by a column, add a count rows column, create a distinct count column, and filter data to count only specific values.

You may also like:

• Merge Tables in Power BI
• Remove Leading Zeros in Power BI
• Convert Number to Text in Power BI
• Sort Slicer by Another Column in Power BI