How to Remove Duplicates on Power BI [Full Guide]

Easy steps to get rid of duplicates in no time

Reading time icon 4 min. read

Readers help support Windows Report. We may get a commission if you buy through our links. Tooltip Icon

Read our disclosure page to find out how can you help Windows Report sustain the editorial team Read more

Key notes

  • On all data sets, duplicates may reduce the integrity of your information and make them heavier to handle.
  • Using easy steps or DAX formulas, you may achieve duplicate free data on Power BI.
power bi remove duplicates

You must have found this guide because you need to remove duplicates on Power BI. We will share with you all you need to know.

Duplicates are a nightmare for anyone who works with data, and they will quickly ruin the integrity of your data.

Microsoft’s Power BI is a dynamic data visualization software application that primarily emphasizes business intelligence. It’s an element of Microsoft’s Power Platform. It also offers multiple ways of handling duplicates.

Does Power BI automatically remove duplicates?

Power BI automatically deletes duplicates when you add data to the data model. Any duplicate rows in the data will be immediately found and removed when you import them. This process is very beneficial;l in the following ways:

  • Automation – Manually eliminating duplicates may be time-consuming and error-prone, particularly if you have a lot of data. This procedure is automated by Power BI, allowing for speedy and precise duplication removal.
  • Customization – Power BI allows you to define the columns you wish to use to find duplicates, allowing you to eliminate duplicates in a manner that suits your particular requirements.
  • Usability – Power BI is simple to use even if you are unfamiliar with data analysis tools because of its user-friendly design.
  • Integration with other products – Power BI connects with various tools and platforms, making it simple to utilize in combination with other applications and linking it to your current data sources.

Overall, utilizing Power BI to eliminate duplicates may help you save time, minimize mistakes, and improve the usability of your data.

How do I remove duplicates in Power BI?

1. Removing Power BI duplicates based on a column

  1. Click on the Home menu and select the Group by option.
    power bi remove duplicates
  2. Select the element you need to group this data by and make sure you choose Max for the Operation value.
  3. Click OK.

This method in Power BI will remove duplicates but keep the latest or last entry.

2. Remove Power BI duplicates from a table using the Remove Duplicate function

2.1 Remove from a single column

  1. Open Power BI and select any table containing duplicates.
  2. Click the column header where you need to remove duplicates.
  3. Now under the Home menu, expand Remove rows and select Remove duplicates.

Looking at this process’s code, you will see that the duplicates are removed only for that column and nothing else.
power bi remove duplicates

You may also select two columns and follow the same steps to remove duplicates on Power BI based on two columns.

Sometimes, you may want to remove duplicates in more than one column. You should try the process listed below.

2.2 Remove from multiple columns (the entire table)

  1. Open Power BI and select any table containing duplicates.
  2. Select the top cell just before your column headers and select Remove duplicates.
  3. This will eliminate duplicates across the table, and you can verify this by looking at the formula produced for this operation.
    power bi remove duplicates

A few users have asked how do I remove duplicates from multiple columns in Power BI. You should know that the solution above is the perfect way to achieve this.

3. Remove duplicates in Power BI with DAX

  1. Use the DISTINCT function with the script below (Here, you have a table called Table1 and a column called Category):
    UNIQUE_Categories = DISTINCT(Table1[Category])
  2. You may also pass arguments in this formula using the script below (This will count the number of unique category values in Table1):
    UniqueCategoryCount = COUNTX(DISTINCT(Table1[Category]), Table1[Category])

Step 1 will create a new table called UNIQUE_Categories that only contains unique values from the Category column of Table 1.

Step 2 returns the number of unique category values in Table 1.

That is as far as we will discuss in this article. Now you should have successfully eliminated duplicates in your data.

Tell us in the comments which of these solutions is most convenient for you.

More about the topics: Power BI