How to Remove Duplicates in Power BI Table Visual

Seeing the same row appear multiple times in a Power BI table can be confusing and makes your report look unprofessional. It's a common roadblock that happens to everyone, but the good news is that it's usually easy to fix once you understand why it's happening. This guide will walk you through the reasons duplicate rows appear and show you three distinct methods to handle them, from a quick visual fix to a robust data cleaning solution.

Graphed

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.

Build Your First Agent

Why Do Duplicates Appear in a Power BI Table?

Before jumping into solutions, it's important to understand the two main reasons you might be seeing duplicates. The right fix depends entirely on the cause.

Reason 1: Data Granularity

Most of the time, what looks like a duplicate isn't a true duplicate in your source data. Instead, it's an issue of granularity. Power BI visuals summarize or aggregate your data based on the fields you add to them. If the columns you've chosen for your table aren't specific enough to define a unique row, Power BI will display what appear to be identical lines.

Imagine you have a sales table with these columns:

• OrderID
• OrderDate
• CustomerName
• Product
• SaleAmount

Now, let's say you create a table visual and only pull in CustomerName and OrderDate. If a customer named "John Smith" bought three different products in separate transactions on the same day, your table would look like this:

Table Visual Fields: CustomerName, OrderDate

Result:

John Smith | 10/26/2023 John Smith | 10/26/2023 John Smith | 10/26/2023

These rows look like duplicates, but they represent three unique transactions. The table just isn't detailed enough to show what makes them different (the OrderID or Product). This is a granularity problem, not a data quality problem.

AI Agents for Marketing

Crash Course

Graphed

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.

Download the PDF

Reason 2: Actual Duplicates in the Source Data

Sometimes, your source data itself is messy. A system glitch, a manual data entry mistake, or a faulty data import could have created genuinely identical rows in your underlying dataset. For example, if the exact same transaction for an order was accidentally recorded twice, including the same OrderID, Product, and SaleAmount, you have a true duplicate.

These require a different approach because they need to be cleaned from your data model to ensure your calculations (like total sales) are accurate.

Now, let's look at how to solve for each scenario.

Method 1: Adjusting the Table's Granularity (The Quick Check)

If you suspect the issue is granularity, the fastest way to confirm and fix it is by adding a unique identifier to your table. This field provides the missing detail that makes each row distinct.

Step-by-Step Instructions:

1. Select your Table visual in the Power BI report canvas.
2. In the Visualizations pane on the right, look at the “Columns” data well where your fields are listed.
3. Go to the Data pane and find a column in your dataset that should be unique for each transaction. This could be an OrderID, TransactionID, or another primary key.
4. Drag this unique identifier column into the “Columns” well of your table visual.

Once you add this field, you should see the once-"duplicate" rows now show different values in the new column. This confirms the issue was just about the level of detail.

When to use this method: Use this as a quick diagnostic tool or when you actually want to display that extra level of detail in your table. If you don't want the ID column cluttering your visual, however, you'll need a more permanent solution for creating summarized lists, which leads us to Method 2.

Graphed

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.

Build Your First Agent

Method 2: Removing Duplicates for Summarized Lists with DAX

Sometimes you don't want to see every single transaction. You just want a clean list of unique values — for instance, a list of all customers who made a purchase this month. Trying to get this from a transaction table will result in duplicates. The solution is to create a new, summarized table in your data model using DAX.

The DISTINCT() function is perfect for this. It scans a column and returns a new single-column table containing only the unique values.

Step-by-Step Instructions to Create a Unique Customer Table:

1. On the left-hand navigation, click the Data view icon to see your data tables.
2. In the main ribbon at the top, navigate to the Table tools tab and click on New table.
3. The formula bar will appear. Enter a DAX formula to create your new table. For a unique list of customers from a 'Sales' table, you would write:
4. Press Enter. Power BI will instantly create a new table called “Unique Customers” that contains a single column with a de-duplicated list of every customer name.

Now you can go back to the Report view and use this "Unique Customers" table to create your summarized table visual, slicers, or charts without worrying about duplicates.

When to use this method: This is ideal when your goal is to create a summarized, unique list to be used in slicers, filters, or visuals that display distinct entities (like counting unique customers) rather than individual transactions.

Method 3: Permanently Removing True Duplicates with Power Query (The Best Practice)

If your investigation reveals that your source data has actual duplicate rows, the best place to clean them is in the Power Query Editor. This method fixes the problem at the source, ensuring all your visuals and calculations are based on clean, accurate data. Changes made in Power Query are applied every time your data refreshes, so it's a "set it and forget it" solution.

AI Agents for Marketing

Crash Course

Graphed

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.

Download the PDF

Step-by-Step Instructions:

Step 1: Open the Power Query Editor

• On the Home tab of the Power BI Desktop ribbon, click the Transform data button. This will open the Power Query Editor in a new window.

Step 2: Select the Query You Want to Clean

• In the Power Query Editor, you'll see a list of your queries (your tables) in the pane on the left. Click on the query that contains the duplicate data.

Step 3: Choose Which Columns to Check for Duplicates

This is the most important step. You need to tell Power Query how to identify a duplicate.

• To remove rows that are identical across all columns: You don't need to select anything. Power Query can check the entire row.
• To remove rows based on specific key columns: If a duplicate is defined by a few specific columns (e.g., same OrderID and same Product should not happen twice), hold down the Ctrl key and click on the headers of those specific columns to select them.

Step 4: Use the "Remove Duplicates" Command

1. With the appropriate columns selected (or no columns selected if checking the whole row), go to the Home tab in the Power Query ribbon.
2. Click the dropdown arrow for Remove Rows.
3. Select Remove Duplicates from the menu.

Power Query will scan your table based on the columns you selected and remove all duplicate entries, keeping only the first occurrence of each unique row. You'll see a new step called "Removed Duplicates" appear in the "Applied Steps" pane on the right.

Step 5: Close and Apply Your Changes

• In the top-left corner of the Power Query Editor, click the Close & Apply button. This will save your data transformation steps, close the editor, and refresh your data model in Power BI with the clean, de-duplicated data.

When to use this method: This is the recommended approach for fixing true data quality issues. It ensures your data model is clean, your measures are accurate, and the fix is automated for all future data refreshes.

Finding the Right Approach for Your Duplicates

Deciding which method to use is simple if you remember the core problem you're trying to solve.

• For visual duplicates due to low detail: Stick with Method 1 to add more granular fields to your table to show why the rows are unique.
• To create a clean, distinct list for a filter or chart: Use Method 2 and create a new summarized table with DAX's DISTINCT() function.
• For actual errors in your source data: Go with Method 3 and use Power Query to remove the duplicates for good. It's the most robust way to ensure data integrity across your entire report.

Final Thoughts

Tackling duplicates in a Power BI table is a fundamental step in building reliable and trustworthy reports. By identifying whether you're facing a granularity issue or a true data quality problem, you can use the right tool - adjusting visual detail, crafting a DAX table, or cleaning data in Power Query - to present your information clearly and accurately.

Mastering these workflows is valuable, but they also highlight how much time can be spent on just preparing data before analysis can even start. We've seen teams spend hours each week doing this kind of cleanup across dozens of tools. That's why we built Graphed. Our goal is to connect to your marketing and sales data sources one time and automate the difficult parts, letting you build clean dashboards simply by describing what you want to see. Instead of manually clicking through menus to remove duplicates, you get real-time, analysis-ready data, freeing you up to uncover insights, not just wrestle with staging your data.