Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Check duplicate in another table

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 n...
Need Help
  • edhans's avatar
    edhans
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

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

 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        edhans
        Community Champion
        6 years ago

        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: