Lire cet article en Français 
How to quickly identify duplicated rows in Power BI with Wiiisdom
Easily identify duplicated rows in Power BI
Duplicated rows in Power BI is a common issue for users and can result in inaccurate reports and insights, ultimately leading to poor decision-making. Manually checking for duplicates is time-consuming and error-prone. This article will demonstrate how you can easily and quickly detect duplicate rows in Power BI with Wiiisdom.
Why does Power BI create duplicate rows?
Duplicated rows in Power BI can occur due to several reasons:
- Many-to-Many Relationships: When two tables are connected with a many-to-many relationship, it can result in duplicated rows. This happens because each row in one table can match multiple rows in the other table.
- Improper Joins: Using joins like LEFT JOIN or INNER JOIN without proper keys can lead to duplication. If the join keys are not unique, Power BI will create multiple combinations of rows.
- Data import errors: Sometimes, errors during data import or refresh can introduce duplicates, especially if the data source itself has duplicates, or people entering multiples times the same information in a system.
Identify and address duplicate data issues with Wiiisdom for Power BI
Duplicated rows in Semantic Layer tables and columns can lead to improper analytics and cause report errors. In our Analytics Governance solution for Power BI, we decided to introduce a new Semantic Layer testing step called “Duplicated Rows” to monitor and proactively alert users when this problem occurs.
Simply provide the fields you want to use for identifying duplicates for a row in the table and Wiiisdom for Power BI will tell you if there are multiple rows with the same values, i.e. duplicates based on your definition, and how many times. For example, if you had a continent dimension table, you could select the continent field to define duplicates. Be aware that in a country dimension table, it would not make sense as multiple countries are on the same continent. Note that for that second use case, you might prefer to use our semantic model statistics step to ensure the proper number of distinct values are met.
View in Wiiisdom for Power BI showing that multiple rows exist with identical values.
This testing step should be part of a continuous monitoring strategy, and not just a one-off. Continually testing helps you go from being reactive to proactive when issues arise.
To configure the step, you can drag & drop the step into your pipeline as shown below:
Add the Duplicated Rows step to your pipeline and choose to identify in one column only or multiples ones.
In Wiiisdom for Power BI, the pipeline execution provides a detailed report of duplications and logs the analysis results, ensuring users can easily identify and address duplicate data issues.
N.B. The report generated in Wiiisdom for Power BI will only show duplicates for the selected field.
Pipeline report example in Wiiisdom for Power BI.
Wiiisdom does the searching for you
It’s as simple as that! In just a few clicks, you can quickly identify duplicate data issues and be proactive in fixing them to ensure accurate and reliable Power BI reports. If you’re interested in seeing in action, get in touch with us today for a demo.
