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 ? ðŸ˜Š

  • 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