Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Find duplicates in a column and give a value

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(CO...
Need Help
  • edhans's avatar
    edhans
    5 years ago

    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.

     

  • edhans's avatar
    edhans
    4 years ago

    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
danishefa's avatar
danishefa
Frequent Visitor
4 years ago

edhans 

 

Could you write a similar code in M language ? ğŸ˜Š

dufoq3's avatar
dufoq3
Community Champion
2 years ago

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