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

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:

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks a lot, it worked like a charm !

      • edhans's avatar
        edhans
        Community Champion
        6 years ago

        Great Anonymous . Glad to help out.