How to write dax to remove duplicates record and show them in the table in Power BI?

by

In Power BI or Excel,

you can use the DAX (Data Analysis Expressions) language to remove duplicates from a table and display the unique records in a new table. To achieve this, you can follow these steps:

  1. Open Power BI or Excel and load the data table you want to remove duplicates from.
  2. Create a new table where you want to display the unique records. To do this, go to the “Model” or “Data” view, right-click on “Tables,” and select “New Table.”
  3. In the formula bar of the new table, write the DAX formula to remove duplicates and display unique records. The formula uses the DISTINCT function, which returns a new table with unique rows based on the specified columns.
  4. The general syntax of the DISTINCT function is: DISTINCT(Table[Column1], [Column2], ...) For example, if you have a table called “Sales” and you want to remove duplicates based on the “Product” and “Date” columns, your DAX formula would be NewTable = DISTINCT(Sales, Sales[Product], Sales[Date])
  5. Press Enter to create a new table with unique records.
  6. Switch back to the “Report” view to see the newly created table with the unique records.

Please note that the original table with duplicates remains unaffected, and the new table will dynamically update whenever changes occur in the source data.

If you’re using Power BI, you can also use the “Remove Rows” option in the Query Editor to remove duplicates record from the source data before loading it into the data model. To do this, follow these steps:

  1. Load the data into Power BI and go to the “Query Editor.”
  2. Select the table you want to remove duplicates from.
  3. Click on the “Home” tab and then click on “Remove Rows.”
  4. From the dropdown menu, select “Remove Duplicates.”
  5. A dialog box will appear, allowing you to choose the columns based on which you want to remove duplicates. Select the columns and click “OK.”
  6. After removing duplicates, click “Close & Apply” to load the cleaned data into the data model.

Remember, remove duplicates record from the source data using the Query Editor affects the loaded data in the entire data model while using DAX creates a separate table with the unique records. Choose the method that best fits your requirements.

Leave a Comment