Check duplicate in another table

[Anonymous]

Hello guys,

I'm trying to create a custom column in my 'investigation' table. I want that this new column tells me if there are some duplicates ERP number in my 'source' table matching with ERP number in my 'investigation' table. How can i do that ?

Best regards,

ScrappyS

[edhans]

The following formula will tell you if the Code in your current table exists in the Code column of the Source Table.

List.Contains(#"Source Table"[Code], [Code])

You can see my sample PBIX file here. 

If you need further help, please provide actual data.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[Anonymous]

Hello Edhans,

Thanks for the answer, but how can i get only the ones in 'source' [code] with an occurence > 1 AND match to my 'investigate'[code] ?

Best regards,

ScrappyS

[edhans]

again, provide sample data, with some examples of expected output. Please don't expect us to come up with sample data. Then we get it wrong, which is what I did with my first try.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[Anonymous]

My bad, i can't provide pbix file for now but maybe with this schema, it will be more precise :

Thank you very much !

[edhans · Accepted solution]

See this formula:

    #"Add Duplicate Check" = 
        Table.AddColumn(
            #"Changed Type",
            "Duplicate Check",
            each
            let 
                varCode = [Code],
                varColor = [Color]
            in
            if Table.RowCount(
                Table.SelectRows(
                    #"Source Table", each [Code] = varCode and [Color] = varColor
                )
            ) > 1 
            then true
            else false,
            type logical
        )

My PBIX file is here. It looks in this table:

and finds duplicates as shown:

[Anonymous]

Thanks a lot, it worked like a charm !

[edhans]

Great @Anonymous . Glad to help out.