Duplicate value

[koorosh]

Hello experts,

How to find repetitious value in the column name and then enter them with their path to a new table?

[Sergii24 · Accepted solution]

Hi @koorosh, the easiest way is to add "calculated column" to your table to count current name occurences:

CountName = 
VAR _NameOnACurrentRow = 'Table'[Name]      //Name at currently iterating row
RETURN
    COUNTROWS(                              //needs to aggregate the table (i.e. find number of rows)
        FILTER(
            'Table',                        //all table "Table"
            [Name] = _NameOnACurrentRow     //filtered with a value of currently iterating row
        )
    )

This will give you the following table:

Now, when creating a new table the only thing you need to do is to filter this table:

Here is DAX code:

Repetitive values = 
    FILTER(
        'Table',
        [CountName] > 1
    )

If you prefer you can avoid creation of calculated column by combining steps in new table definition

Repetitive values = 
VAR _TableWithCount =
    ADDCOLUMNS(
        'Table',
        "@CountName", 
            VAR _CurrentName = 'Table'[Name]
            RETURN
                COUNTROWS(
                    FILTER( 
                        'Table', 
                        [Name] = _CurrentName 
                    )
                )
    )

RETURN
    FILTER(
        _TableWithCount,
        [@CountName] > 1
    )

Good luck with your work! 🙂

[koorosh]

Hi Sergii,

The first calculated column make the following result:

And the create table make the following:

But now we have a table with duplicated rows. We need just one row for John and one row for Mike???

[koorosh]

It should be as the following