Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Find Duplicates in Power BI

Cody Schneider••9 min read

Catching duplicate entries in your data is a common but vital step for accurate analysis. If left unchecked, duplicates can quietly inflate your core metrics, lead to incorrect conclusions, and undermine the trust your team has in your dashboards. This guide will walk you through several straightforward methods for finding and dealing with duplicates directly within Power BI, using both the Power Query Editor and DAX formulas.

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 Dealing with Duplicates Matters

Picture this: you pull sales data and notice that your revenue for a specific product seems unusually high for the month. You dig in and realize a single, large order was recorded twice because of a system glitch. Suddenly, your "record-breaking month" is just an average one. This is the danger of duplicates in a nutshell.

Ignoring duplicate rows can lead to several problems, including:

  • Skewed Calculations: Metrics like SUM, COUNT, and AVERAGE will be wrong. Duplicate sales entries will inflate revenue totals, while duplicate survey responses could skew customer satisfaction scores.
  • Misleading Visuals: Your charts and graphs will reflect the flawed data, presenting an inaccurate picture of business performance.
  • Relationship Issues: In a relational data model, duplicates in a dimension table (like a list of customers) can cause reporting errors when connected to a fact table (like sales transactions).

Ultimately, cleaning up duplicates is a foundational step in data preparation. It ensures that your analyses are built on a bedrock of reliable, accurate information, so you can make decisions with confidence.

Method 1: Using Power Query for a Quick and Easy Fix

The Power Query Editor is Power BI’s built-in tool for extracting, transforming, and loading (ETL) data. It's the ideal place to handle structural data issues like duplicates before the data even reaches your data model. By cleaning your data here, you ensure that every visual and calculation in your report starts from a clean slate.

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.

Step-by-Step: Removing Duplicates

This is the most direct approach. It identifies and removes entire rows that are exact copies based on the column(s) you select. This is a "destructive" action within Power Query, as it alters the dataset that gets loaded into your model (though your original source file remains untouched).

  1. Open the Power Query Editor: From the Power BI main window, go to the Home tab and click on Transform Data.
  2. Select Your Column(s): In the Power Query window, click the header of the column you want to check for duplicates. To check for full row duplicates, select all columns by clicking the table icon at the top-left or pressing Ctrl + A. If uniqueness is defined by a combination of columns (e.g., Order ID and Product ID), hold Ctrl and click each of those column headers.
  3. Find the "Remove Duplicates" Option: Go to the Home tab, click the Remove Rows dropdown, and select Remove Duplicates.

Power Query will immediately remove the duplicate rows, keeping only the first instance it finds for each unique value or combination of values. A new step will be added to your "Applied Steps" list on the right, which you can easily undo if you make a mistake.

Step-by-Step: Keeping Duplicates for Review

Sometimes you don't want to just remove the duplicates - you want to isolate them to understand why they appeared in the first place. This is useful for auditing and debugging data entry problems.

  1. Open Power Query and Select Column(s): Follow the same first two steps as above.
  2. Choose "Keep Duplicates": In the Home tab, go to the Keep Rows dropdown and select Keep Duplicates.

The table will now be filtered to show only the duplicated entries. This is a great way to inspect the problem rows before deciding how to handle them. For example, you might see that all duplicates came from a specific data source or were entered on a certain day.

Method 2: Identifying Duplicates Without Deleting Them (Power Query)

A non-destructive approach is often safer because it preserves your original data while adding extra context. Instead of deleting rows, you can add a new column that simply flags them as "Original" or "Duplicate." This is incredibly useful for reports where you need to show the full, raw dataset but also call out potential quality issues.

Using the Group By Feature to Find Problem IDs

The core logic here is to group your data by a unique identifier (like an OrderID) and count how many rows fall into each group. If a group has a count greater than 1, you know you have duplicates.

  1. Open Power Query and Select a Key Column: In the Power Query editor, select the column(s) that are supposed to be unique (e.g., InvoiceNumber). We'll use this as our key.
  2. Group the Data: Navigate to the Transform tab and click Group By.
  3. Configure the Grouping: A new window will appear.
  4. Click OK: You'll now have a new, summarized table showing each unique ID and how many times it appeared in your original data.
  5. Filter for Duplicates: Click the filter arrow on your new "RecordCount" column, select Number Filters, and choose Greater Than. Enter "1" in the box.

The result is a clean list of all the IDs that have duplicate entries in your source table. You can then use this list to investigate further.

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

Adding a Conditional Column to Flag Every Duplicate Row

Building on the grouping method, you can merge that duplicate count back into your main table to flag each duplicate row individually. This is arguably the most robust way to handle duplicates in Power Query.

  1. Create the Grouped Count Table: Follow the steps in the "Using the Group By Feature" section above to create a table that counts occurrences. Let’s assume your original table is named Sales and this new query is named Duplicate Check.
  2. Merge Queries: Go back to your original Sales query. In the Home tab, click Merge Queries.
  3. Configure the Merge:
  4. Expand the Merged Column: A new column will appear with "Table" in each cell. Click the expand arrows icon in the column header. Uncheck all boxes except for your "RecordCount" column. Deselect "Use original column name as prefix." Click OK.
  5. Add the Final Flag: Your original table now has a column showing how many times each row's ID appears. Go to the Add Column tab, click Conditional Column, and set up a rule:

You now have a clean, easy-to-filter column that flags every single problematic row without having deleted any data. You can filter this column in your Power BI reports to show or hide duplicates as needed.

Method 3: Finding Duplicates with DAX

While Power Query is best for pre-processing, sometimes you need to find duplicates within the data model itself, maybe on a column that you created after loading the data. Data Analysis Expressions (DAX) is the formula language used in Power BI's reporting layer, and it's perfect for this scenario. The two main ways to use it are calculated columns and measures.

Creating a Calculated Column to Flag Duplicates

This approach is similar to the conditional column in Power Query but it's calculated on-the-fly inside your data model.

  1. Go to the Data view in Power BI (the grid icon on the left).
  2. Select the table you want to check.
  3. From the Table tools ribbon, click New Column.
  4. Enter the following DAX formula in the formula bar, replacing 'Sales'[OrderID] with your table and column name:
IsDuplicate = 
VAR CurrentRowID = 'Sales'[OrderID]
VAR IDCount =
    CALCULATE(
        COUNTROWS('Sales'),
        'Sales'[OrderID] = CurrentRowID
    )
RETURN
    IF(IDCount > 1,  "Duplicate", "Unique")

How it works:

  • VAR CurrentRowID = 'Sales'[OrderID] stores the ID of the current row being evaluated.
  • VAR IDCount = ... counts how many rows in the entire 'Sales' table have the exact same ID as the current row.
  • RETURN IF(... ) returns "Duplicate" if the count is more than one, and "Unique" otherwise.

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.

Creating a Measure to Count Duplicates

What if you just want a single number for a KPI card, like "Total Duplicate Invoices"? A DAX measure is ideal for this because it doesn't add a new column to your table, it just calculates a value to be used in visuals.

  1. Go to the Report view in Power BI.
  2. Make sure no visuals are selected. In the Home tab, click New measure.
  3. Enter this formula, again adjusting for your table and column names:
Duplicate ID Count =
COUNTROWS(
    FILTER(
        VALUES('Sales'[OrderID]),
        CALCULATE(COUNT('Sales'[OrderID])) > 1
    )
)

How it works:

  • VALUES('Sales'[OrderID]) creates a unique list of all Order IDs.
  • FILTER(...) iterates through that list, for each ID, CALCULATE(COUNT('Sales'[OrderID])) counts its occurrences in the full table.
  • It keeps only IDs where that count is greater than 1.
  • Finally, COUNTROWS(...) counts how many IDs meet this criterion, giving your total number of duplicated IDs.

Power Query vs. DAX: Which Should You Use?

Both methods are effective, but they serve different purposes. Knowing when to use each one will make your workflow more efficient.

Use Power Query when:

  • You want to clean and transform the data before it's loaded into the model. This is generally the best practice for data integrity.
  • You need to permanently remove or filter out duplicates as part of your data ingestion process.
  • Performance is a major consideration. Processing data upstream in Power Query is usually more efficient than running complex DAX calculations for every user interaction in the report.

Use DAX when:

  • You don't have permission to edit the Power Query steps or need a solution that doesn't modify the underlying data table structure.
  • Your definition of "duplicate" is dynamic and depends on report slicers or filters for its context.
  • You simply need to count or flag duplicates for use in specific visuals, not for a fundamental data clean-up.

Final Thoughts

Cleaning duplicates is a critical step in creating powerful and trustworthy reports in Power BI. Using Power Query is best for preparing your data at the source, ensuring a clean foundation for your model. On the other hand, DAX provides the flexibility to perform dynamic duplicate analysis directly within your interactive dashboards. The right method depends on whether your goal is permanent data cleansing or flexible in-report flagging.

Managing data quality hiccups, like finding duplicates, is just one small battle in the much larger war of data reporting. A lot of time is still dedicated to connecting data sources, building reports, refreshing data, and trying to pull insights from a dozen different tools. We believe this should be easier, so we built Graphed. Our platform connects your marketing and sales sources in one place, giving you an AI data analyst to do the heavy lifting. Instead of building manual Power BI checks, you could just ask, "Show me last month's sales by region, removing any duplicate orders," and get a live, interactive visualization in seconds, letting you get straight to the impactful insights.

Related Articles