Forum Discussion

vanik85's avatar
vanik85
Icon for Helper I rankHelper I
3 years ago
Solved

How to find duplicate values count by matching 2 columns

Hi    I have been trying to identity duplicate values by matching 2 or more columns in power bi   e.g Month.    Mobile number    insertion date    UTM Apr.               xxx.                   ...
Need Help
Ahmedx's avatar
Ahmedx
Icon for Super User rankSuper User
3 years ago

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

https://1drv.ms/u/s!AiUZ0Ws7G26RiR5EgcMrD5D2XNCX?e=cfB8GX

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

  • vanik85's avatar
    vanik85
    Icon for Helper I rankHelper I
    3 years ago

    Thanks! Big help! 

    have a follow up question which will explain 

     

    incase  we wish to remove the duplicates at the time of data consolidation 

     

    e.g 

    source file 1    Mobile number 

    file 1.               xxx

    file 1.               yyy

    file 2.               zzz

    file 2.               zzz 

     

    Duplicate to be removed by checking values in  file 1 and duplicate mobile number to be dropped post the first insertion date

    • Ahmedx's avatar
      Ahmedx
      Icon for Super User rankSuper User
      3 years ago

      I don't understand, please explain again with an example.

      • vanik85's avatar
        vanik85
        Icon for Helper I rankHelper I
        3 years ago

        Source file1 having fields 

         

        Mobile number.   Insertion date.   UTM 

        xxx.                         01-Apr-23.       Paid 

        xxx.                         02- Apr-23.       Organic

        yyy.                         03-Apr-23.        Organic

         

        Source file 2 

         

        Mobile number.   Insertion date.   UTM 

        xxx.                         02-May-23.       Paid 

        xxx.                         03- May-23.       Organic

        yyy.                         03-May-23.        Organic

        zzz.                         04-May-23.         Paid

         

         

        Currently I have been picking up these above sources from folder and merging them as one and then applying certain queries. However on applying remove duplicates below is the data set that I get

         

        Mobile number.   Insertion date.   UTM 

        xxx.                         01-Apr-23.       Paid 

        yyy.                         03-Apr-23.        Organic

        zzz.                         04-May-23.         Paid

         

        ask is to get below 

         

        Mobile number.   Insertion date.   UTM 

        xxx.                         01-Apr-23.       Paid 

        yyy.                         03-Apr-23.        Organic

        xxx.                         02-May-23.       Paid 

        yyy.                         03-May-23.        Organic

        zzz.                         04-May-23.         Paid