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(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
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

10 Replies

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    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.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      edhans
      Icon for Community Champion rankCommunity Champion
      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
      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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's avatar
      edhans
      Icon for Community Champion rankCommunity Champion
      4 years ago

      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.

    • dufoq3's avatar
      dufoq3
      Icon for Community Champion rankCommunity 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