How to Count Duplicates in Power BI

Finding duplicate values in your data is a common but critical step in any analysis. Duplicates can throw off your metrics, leading to inaccurate reports and flawed business decisions. In Power BI, correctly identifying and counting these instances is a fundamental skill. This tutorial will walk you through several methods for counting duplicates using both Power Query and DAX.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Counting Duplicates is Important

Before we get into the "how," let's quickly cover the "why." You might need to count duplicate entries for several reasons:

• Data Quality Checks: Identifying duplicate customer records, order numbers, or product SKUs is a crucial part of data cleaning. You can't trust your reports if the underlying data is unreliable.
• Accurate Calculations: If you're counting unique customers, a duplicate entry could inflate your numbers and give you a false sense of growth.
• Understanding Data Entry Issues: A high number of duplicates can signal problems in your upstream data entry processes, whether it's a faulty web form or manual input errors.

Power BI gives you two primary environments to tackle this: the Power Query Editor for data transformation and the main report view for analysis with DAX. We'll cover both.

Method 1: Using Power Query Editor

The Power Query Editor is the best place to handle duplicates during the data preparation phase. The logic here is simple: clean your data before it ever gets loaded into your data model. This approach is generally more efficient and keeps your DAX measures simpler.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Finding Duplicates with "Group By"

The "Group By" feature is a powerful way to summarize your data and, in this case, count occurrences of each value in a column.

Let's say we have customer data, and we want to find out if any email addresses are listed more than once.

Step 1: Open the Power Query Editor In Power BI Desktop, go to the Home ribbon tab and click on Transform data. This will launch the Power Query Editor.

Step 2: Select the Column and "Group By" Find and select the table you want to work with in the left-hand Queries pane. Then, select the column you want to check for duplicates (e.g., 'Email'). Navigate to the Home tab or the Transform tab and click on Group By.

Step 3: Configure the Group By Operation A dialog box will appear. Here's how to set it up:

• The column you selected ('Email') will already be listed as the grouping key.
• New column name: Type a name for your count column, like "Count of Emails".
• Operation: Select Count Rows from the dropdown menu.
• Column: This field will be grayed out because we are simply counting rows for each group.

Click OK. Power Query will transform your table, showing a unique list of emails and how many times each one appeared in the original dataset.

Step 4: Filter for Duplicates Now that you have the counts, you can easily filter this table to show only the duplicates. Click the dropdown arrow on your new "Count of Emails" column, go to Number Filters, and select Greater Than. Enter "1" in the box and click OK.

You now have a clean list of all the email addresses that appear more than once and their exact count. From here, you can decide whether to remove them or flag them for review. If this was just for informational purposes, you can right-click the query in the Queries pane and disable the load to prevent it from cluttering your data model.

Method 2: Using DAX for In-Report Analysis

Sometimes, you don't want to change the underlying data. You might need to keep all rows but dynamically count duplicates for a specific visual or measure in your report. This is where DAX (Data Analysis Expressions) comes in.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Creating a Calculated Column to Count Occurrences

A calculated column adds a new column to your table where each row is evaluated based on a formula. This is a great way to "tag" each row with its duplicate count.

Step 1: Go to the Data View After loading your data, click on the Data view icon (it looks like a table) in the left-hand pane of Power BI Desktop.

Step 2: Create a New Column Select the table you want to work with. In the ribbon, under Table Tools, click on New column.

Step 3: Enter the DAX Formula A formula bar will appear at the top. We will use a combination of COUNTROWS and FILTER to find the number of times each value appears. Let's stick with our 'Email' column from the 'SalesData' table example.

Enter the following formula:

Duplicate Count = 
COUNTROWS(
    FILTER(
        SalesData,
        SalesData[Email] = EARLIER(SalesData[Email])
    )
)

Let's break down what this does:

• EARLIER(SalesData[Email]): This is the key to the formula. For each row in the table, EARLIER gets the email address for that specific row.
• FILTER(...): This function then creates a temporary table. It iterates through the entire 'SalesData' table and keeps only the rows where the email matches the email from the EARLIER function (the current row).
• COUNTROWS(...): Finally, this function simply counts the number of rows in the temporary table created by FILTER.

After you press Enter, a new "Duplicate Count" column will be added. For every row, it will show how many times the email in that row appears in the entire table.

Visualizing the DAX Results

Now that you have this calculated column, you can easily use it in your report.

1. Navigate back to the Report view.
2. Add a Table visual to your canvas.
3. Drag the column you checked (e.g., 'Email') and your new 'Duplicate Count' column into the visual.
4. To see only the duplicates, select the table visual, go to the Filters pane, drag 'Duplicate Count' into the "Filters on this visual" well, select "is greater than," and enter "1".

You now have a dynamic table in your report that lists all duplicated items.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Creating a DAX Measure (Advanced)

What if you just want a single KPI card that shows the total count of unique values that are duplicates? For example, "How many distinct products have duplicate entries?" A calculated column isn't right for this, you need a measure.

Go to the Home tab and click New Measure. Enter this formula:

Count of Duplicated Values = 
COUNTROWS(
    FILTER(
        VALUES('SalesData'[Email]),
        CALCULATE(COUNT('SalesData'[Email])) > 1
    )
)

This formula is more advanced:

• VALUES('SalesData'[Email]): Creates a temporary single-column table containing only the unique email addresses.
• FILTER(...): Iterates over this table of unique emails.
• CALCULATE(COUNT('SalesData'[Email])) > 1: For each unique email, this calculates its total count in the original 'SalesData' table and checks if that count is greater than 1.
• COUNTROWS(...): Counts how many unique emails passed the filter check - that is, how many unique emails have duplicates.

You can drop this measure into a card visual to get a top-level summary of your data quality.

Choosing the Right Method

So, which approach should you use? Here’s a simple rule of thumb:

• Use Power Query when your goal is to clean, remove, or permanently flag duplicate data before it ever reaches your report. This is generally the best practice for improving model performance and ensuring data quality.
• Use a DAX Calculated Column when you need to identify and analyze duplicates within the context of your interactive report, without altering the raw data. This is useful for building visuals that let users explore data quality.
• Use a DAX Measure when you need a high-level aggregate summary of your duplicate situation, like for a KPI on a dashboard.

Final Thoughts

Knowing how to count duplicates is an essential skill for anyone serious about data analysis in Power BI. Using Power Query is best for preparing your data and fixing issues at the source, while DAX provides the flexibility to create dynamic calculations and visuals directly in your reports. Mastering both methods will give you the control you need to ensure your data is accurate and trustworthy.

While tools like Power BI are incredibly powerful, they still involve a significant learning curve with DAX formulas and data modeling steps just to answer simple questions about data quality. At Graphed, we've simplified this process by connecting directly to your marketing and sales platforms (like Shopify, Google Analytics, and Hubspot). Instead of writing complex queries or building Power BI models, you can just ask in plain English, "Show me a count of every duplicate order number from Shopify," and get an instant, real-time report. You can start creating dashboards and getting answers from all your data in seconds with Graphed.