Forum Discussion

jpcbr's avatar
jpcbr
Regular Visitor
9 years ago
Solved

Detecting duplicates by validating two column

Hello :)

 

Im new at power bi, so im sorry for the noob question :P

 

So i have a table more or less like this

 

CompanyStage
microsoftMAL
asusMQL
toshibaSAL
lgMAL
asusMQL
nokiaMAL

 

As you can see i have the company "asus"  its duplicated, but i just want to count it once.
So i need to create a new column that verifies if the company its duplicated and if it haves the same stage and give me a result like this:

 

CompanyStageStage verified
microsoftMALMAL
asusMQLMQL
toshibaSALSAL
lgMALMAL
asusMQL 
nokiaMALMAL

 

Thanks  :)

Need Help
  • v-caliao-msft's avatar
    v-caliao-msft
    9 years ago

    jpcbr,

     

    Please refer to the steps below to achieve your requirement.

    1. Add a index column.(Edit Query)
    2. Create a column to combine company and stage column.
      CompanyStage = Table1[Comapny]&Table1[Stage]
    3. Create your expected column.
      outputcolumn = IF(RANKX(FILTER(Table1,Table1[CompanyStage]=EARLIER(Table1[CompanyStage])),Table1[Index],,ASC)>1,BLANK(),Table1[Stage])

    Regards,

    Charlie Liao

3 Replies

  • v-caliao-msft's avatar
    v-caliao-msft
    Microsoft Employee
    9 years ago

    jpcbr,

     

    Please refer to the steps below to achieve your requirement.

    1. Add a index column.(Edit Query)
    2. Create a column to combine company and stage column.
      CompanyStage = Table1[Comapny]&Table1[Stage]
    3. Create your expected column.
      outputcolumn = IF(RANKX(FILTER(Table1,Table1[CompanyStage]=EARLIER(Table1[CompanyStage])),Table1[Index],,ASC)>1,BLANK(),Table1[Stage])

    Regards,

    Charlie Liao

    • jpcbr's avatar
      jpcbr
      Regular Visitor
      9 years ago

      Thats very well thought!!! Thanks a lot :D

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    That is going to be challenging as presented, because there is no way to differentiate the two rows of asus,mql.

     

    If you don't need the dupes, I would just remove them (via Edit Queries).  If you do... what if you had a column that just counted the # of duplicates?  That's easy... :)