Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Detect errors by comparing row values in power BI

Hello,

I have data like follows:

 

 

Here I'm trying to detect rows in which I have errors similar to this one framed in red.

In my client file I have 

Obviously the error is in the second row but how to detect it and extract it in Power BI for correction. 

I have tried to do it as duplicate rows using DAX by counting the number of occurences of the adresse email but this does not work.

 

Can this be done whether in Dax or Power Quzery, and how?

Greg_Deckler BA_Pete 

2 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    You could build table with the unique combinations of client ID, email and name like

    Unique values =
SUMMARIZE ( 'Table', 'Table'[Client ID], 'Table'[Email', 'Table'[Name] )

    Then build a measure

    Num unique values = COUNTROWS( 'Unique values' )

    Create 3 separate table visuals, one for each column of the unique values table and add the measure. Filter the tables so that they only show when the measure is greater than 1 and that should show all the entries where there are duplicates.

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    Hi Anonymous ,

     

    In Power Query:

    Multi-select (Ctrl+click) the [ClientID], [email], and [fullname] columns.

    Go to the Home tab > Remove Rows > Remove Duplicates.

    Select the [ClientId] column and go to the Home tab > Keep Rows > Keep Duplicates.

     

    Pete