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:
- In Power BI Desktop, under the Home tab, click on Transform data to open the Power Query Editor.
- Once the Power Query Editor opens, select the column you want to group by. In this example, select the Product column.
- Right-click on the selected column (Product) and select Group By from the menu.
- 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.
- 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:
- In Power Query Editor, select the column you want to group by. For example, select the Country column.
- Right-click