Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Highlight Duplicates in Power BI

Cody Schneider••8 min read

Finding duplicate entries in your data is one of those nagging tasks that can either be a minor nuisance or a major analytics disaster. While some duplicates are expected, unwanted ones can inflate your sales figures, skew your customer counts, and lead you to make decisions based on faulty information. This guide will walk you through a few practical methods to identify and highlight duplicates directly in Power BI, so you can clean your data and trust your reports.

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

Why Finding Duplicates Matters

Before diving into the "how," it's worth a quick reminder of "why" this is so important. Clean, duplicate-free data is the foundation of reliable reporting. Unaddressed duplicates can cause significant problems, including:

  • Inflated Metrics: Imagine reporting your monthly revenue, but one-off invoicing errors created duplicate sales entries. Your numbers would look great, but they wouldn't be real.
  • Inaccurate Customer Analysis: If you have multiple entries for the same customer (perhaps due to typos or different email addresses), your 'customer count' is wrong. This throws off crucial metrics like customer acquisition cost (CAC) and lifetime value (LTV).
  • Faulty Business Logic: Reports on things like "unique website users" or "first-time purchasers" are immediately compromised by duplicate records that should represent a single entity.

Taking the time to find and manage these duplicates isn't just a data cleaning chore, it's a critical step toward making accurate, data-driven decisions.

Method 1: Identify Duplicates with Power Query Editor

If your goal is to quickly find which values are duplicated without necessarily visualizing them in a final report, the Power Query Editor is your best starting point. This method is great for initial data exploration and cleaning.

Let's say you have a simple table of customer orders and you suspect you have duplicate Order IDs.

Step 1: Open the Power Query Editor

In Power BI Desktop, navigate to the Home tab and click on Transform data. This opens the Power Query Editor, which is where you'll do most of your data preparation work.

Step 2: Use the "Group By" Feature

Select the column (or columns) you want to check for duplicates. For this example, we'll check the OrderID column.

  • With the OrderID column selected, go to the Transform tab in the ribbon.
  • Click on the Group By button.

Step 3: Configure the Grouping

The "Group By" dialog box will appear. Here’s how to set it up:

  • Your selected column (OrderID) will already be there as the basis for grouping.
  • For the New column name, type something intuitive like "Count".
  • For the Operation, choose Count Rows from the dropdown.
  • The Column field can be left as is, since we're just counting rows within each group.

Click OK.

Step 4: Analyze the Results

Power Query will now transform your table to show each unique OrderID and a new "Count" column showing how many times it appeared in the original data. You can easily filter the "Count" column to show only values greater than 1. Everything that shows up is a duplicate in your original dataset.

Note: This method modifies your table within Power Query to show the counts. If you want to keep your original data intact for a report while still highlighting the duplicates, Method 2 is what you need.

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Method 2: Create a Helper Column Using DAX

This is the most powerful method because it allows you to dynamically identify duplicates at the row level without altering your base data. You’ll create a new calculated column in your table using DAX (Data Analysis Expressions). This "helper column" will count the occurrences of a value, which you can then use for filtering and conditional formatting.

Step 1: Navigate to the Data View

In Power BI Desktop, click on the Data icon in the left-hand navigation pane to view your tables.

Step 2: Create a New Column

Select the table you want to check. In the Column tools tab that appears in the ribbon, click on New column.

Step 3: Write the DAX Formula for a Single Column

Let’s start by finding duplicates in a single column, like an Email address.

In the formula bar, enter the following DAX formula. Replace 'YourTableName' with the actual name of your table and [Email] with the column you're checking.

Duplicate Check = 
CALCULATE(
    COUNT('YourTableName'[Email]),
    FILTER(
        'YourTableName',
        'YourTableName'[Email] = EARLIER('YourTableName'[Email])
    )
)
GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

Breaking Down the Formula:

  • 'YourTableName'[Email]: Specifies the column to check.
  • EARLIER('YourTableName'[Email]): Gets the email address from the current row being evaluated.
  • FILTER(): Creates a subset of the table where the email matches the current row's email.
  • COUNT(): Counts how many rows are in that filtered subset.

The result? If an email appears only once, the count will be 1. If it's a duplicate and appears three times, the count will be 3 for each of those three rows.

Step 4: Expand the Formula for Multiple Columns

Often, a duplicate isn't defined by just one column. For example, a duplicate sale might be the same ProductID sold to the same CustomerID on the same Date. You can modify the formula to account for this using the && operator (which means AND):

Multi-Column Duplicate Check =
CALCULATE(
    COUNTROWS('YourTableName'),
    FILTER(
        'YourTableName',
        'YourTableName'[CustomerID] = EARLIER('YourTableName'[CustomerID]) &&
        'YourTableName'[ProductID] = EARLIER('YourTableName'[ProductID]) &&
        'YourTableName'[Date] = EARLIER('YourTableName'[Date])
    )
)

This formula now counts the rows where all three conditions are identical to the current row.

Method 3: Visualize Duplicates with Conditional Formatting

You’ve created the helper column, now it's time to put it to use. Conditional formatting lets you automatically highlight the duplicate rows in your report visuals, making them instantly obvious to anyone viewing the dashboard.

Step 1: Create a Table Visual

Go to the Report view in Power BI. Add a Table visual to your canvas and drag in the columns you want to display, including your new "Duplicate Check" helper column.

Step 2: Add Conditional Formatting

  • In the Visualizations pane, find the list of fields in your table.
  • Right-click on the field you want to apply the color to (e.g., CustomerID).
  • From the context menu, select Conditional formatting > Background color.

Step 3: Set Up the Formatting Rule

The "Background color" window will open. Configure it as follows:

  • Format style: Choose Rules.
  • What field should we base this on?: Select your helper column (e.g., "Duplicate Check").
  • Define the rule: Set up a rule like this: If value is greater than 1 (as a Number) and is less than or equal to a large number like 100 (or you can set it to only be greater than 1). Then select a color, like a light red.

Click OK.

Immediately, all rows that your DAX formula identified as duplicates (i.e., with a count greater than 1) will be highlighted in your table, making them impossible to miss.

Bonus Tip: Create a "Duplicates Only" View

You can also use your "Duplicate Check" column to easily create a report view that only shows you the duplicate records. Simply drag the "Duplicate Check" column into the Filters pane for your table visual or the entire page, and set the filter to "is greater than 1".

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

What to Do Next: Addressing the Duplicates

Finding the duplicates is only half the battle. Now you have to decide what to do with them.

1. Remove Duplicates Permanently in Power Query

If you've determined the duplicates are junk and should be removed entirely, you can do this easily in the Power Query Editor. Just select the column(s) that define the duplicate, right-click the column header, and select Remove Duplicates. Be careful with this - it permanently removes the data from your Power BI model, so be sure that's what you want!

2. Investigate the Source System

In most cases, duplicates in your report are a symptom of a deeper problem in your source data. Whether it's your Salesforce CRM, Shopify store data, or a manually managed spreadsheet, the best long-term solution is to fix the data entry issues at the source. Power BI helped you find the problem, now you can go back to the source system and merge the records, correct the typos, or fix the automation that's creating them.

Final Thoughts

Finding and highlighting duplicates in Power BI is a common task that can seem intimidating, but as shown here, you have several powerful options. Whether you prefer a quick count with Power Query or a more flexible visual highlighting with DAX, these methods will help you maintain clean data and generate trustworthy reports for your team.

Frankly, this kind of data cleaning and validation is where a lot of time bleeds away when building reports. Before you can even answer a simple question like "which of our marketing campaigns are driving sales?", you first have to pull data from a dozen places, stitch it all together, and scrub for inconsistencies like the duplicates we just tackled. We built Graphed to automate that entire messy process. By connecting your tools directly, you can ask questions in plain English and get real-time dashboards and answers, letting our AI-powered analyst handle the tedious data wrangling for you.

Related Articles