Find duplicates in a column and give a value

[Anonymous]

Hi All,

i want to create a calculates column with specific value when duplicates are found.

if the colum has duplicates give "duplicated"

ths formula i have used is:

Formula = IF(CALCULATE(COUNTROWS('04Space'), ALL('04Space'[Name])) < 1, "DUCPLIATED")

but the new column is blank

on the other hand, if i can give also "uniques" for the non ducpliates values will be great.

Thanks community

[edhans · Accepted solution]

You can use this in a Calculated Column @Anonymous 

Duplicates = 
VAR varCurrentValue = 'Sample'[Column1]
VAR varInstances = 
    COUNTROWS(
        FILTER(
            'Sample',
            'Sample'[Column1] = varCurrentValue
        )
    )
var Result = 
    IF(
        varInstances > 1,
        "Duplicate",
        "Unique"
    )
RETURN
    Result

You don't need ALL or CALCULATE. ALL in this context removes filters. Tables and Calculated Columns have no filter context, only row context.

[TimWilsens]

Works as a charm!! Thanks!!

[danishefa]

@edhans 

Could you write a similar code in M language ? 😊

[dufoq3]

@danishefa,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YGRTmDSGUy6IJGuYNINSSWQHQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t]),
    Ad_DuplicateUnique = Table.AddColumn(Source, "Duplicate/Unique",  each if List.Count(List.Select(List.Buffer(Source[Value]), (x)=> x = [Value])) > 1 then "Duplicate" else "Unique", type text)
in
    Ad_DuplicateUnique

[edhans]

I would generally not recommend this in Power Query @danishefa as it would need to do a table scan. If it was a few hundred rows or perhaps low thousands, it might perform ok, but if more than that, even partitioning data Power Query bogs down, and it would be best done upstream in the source, or downstream in DAX.

[Anonymous]

HI,

this help worked. however, now i need an step worward.

is it possible to add into the formula exceptions??

for example give all duplicates except "house" & "table"

Regards

[edhans · Accepted solution]

You can try this:

Duplicates =
VAR varCurrentValue = 'Sample'[Column1]
VAR varInstances =
    COUNTROWS(
        FILTER(
            'Sample',
            'Sample'[Column1] = varCurrentValue
                && NOT 'Sample'[Column1]
                IN {
                "House",
                "Table"
            }
        )
    )
VAR Result =
    IF(
        varInstances > 1,
        "Duplicate",
        "Unique"
    )
RETURN
    Result

[Anonymous]

Thanks  @edhans,
just last question, how would you change the "in" for "contain" to avoid all the words containing "house"?

Thanks very much for your help

[edhans]

I would look at either CONTAINS or CONTAINSTRING

I'd need sample data to work with to test though. You might start a new thread. this one was solved almost a year ago and solved threads get less attention, so it is usually better to start a new thread with good sample data.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[Anonymous]

@edhans 

[edhans · Accepted solution]

You can use this in a Calculated Column @Anonymous 

Duplicates = 
VAR varCurrentValue = 'Sample'[Column1]
VAR varInstances = 
    COUNTROWS(
        FILTER(
            'Sample',
            'Sample'[Column1] = varCurrentValue
        )
    )
var Result = 
    IF(
        varInstances > 1,
        "Duplicate",
        "Unique"
    )
RETURN
    Result

You don't need ALL or CALCULATE. ALL in this context removes filters. Tables and Calculated Columns have no filter context, only row context.