Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Anonymous
Not applicable

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
2 ACCEPTED SOLUTIONS
edhans
Super User
Super User

You can use this in a Calculated Column @Anonymous 

edhans_0-1628611372687.png

 

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

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


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

10 REPLIES 10
TimWilsens
Regular Visitor

Works as a charm!! Thanks!!

danishefa
Frequent Visitor

@edhans 

 

Could you write a similar code in M language ? 😊

@danishefa,

 

dufoq3_0-1707509264267.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

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

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


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

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

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

edhans
Super User
Super User

You can use this in a Calculated Column @Anonymous 

edhans_0-1628611372687.png

 

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.