Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Move duplicates from one column to another column

Hi,

 

My data consists of:

- A company name (Column1)

- A company code (Column2)

- An IP-address (Column 3)

 

Some companies share a data center and therefore have 1 outgoing IP-address.

What I want is to remove the duplicate rows (based on IP-address)  from the companies they belong to and move them to a another company (that I need to create as some sort of 'other' category).

 

I tried some stuff in the power query editor but got "Expressions.Error" that the name (I tried it with an IF and with a CALCULATE statement) was not recognized. Am I using the wrong language in the wrong editor?

 

Thx in advance!

  • TomMartens's avatar
    TomMartens
    8 years ago

    Hey,

     

    this will do what you are looking for in the QueryEditor

     

    1. Group your table
      From the Transfor menu select "Group by"
    2. Expand the table in the "dummy" column




      Finally you can create a custom column, that assigns the "Z-company" if the value of the Count column is gt 1 otherwise return the value from the Company code column, like so:
    3. Create custom column
      From the menu "Add column" choose "Custom column"






     

    Hopefully this is what you're looking for, here is a little screenshot of my final table using your sample data:

     

    Regards,
    Tom

     

6 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    8 years ago

    Hey,

     

    yes the wrong language in the wrong editor IF and CALCULATE are DAX functions :smileywink:

    if (really, all lowercase :-) ) can be used in the QueryEditor, meaning the language is M

     

    Maybe you can provide en Excel file with some sample data that also contains a column with the result you expect, upload the sample file to onedrive or dropbox and share the link.

     

    Regards,

    Tom

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi,

       

      So this editor is only for M? Than where can I use DAX funxctions?

      Below you can find an example of the data I use.

      As you can see every company has a unique company code but an IP-adress can be shared between companies (for example if they have a contract with the same data center). 

       

      What I want is to remove the rows that have an a duplicate IP-address and move them all to a newly created 'fake' company (for example Z with company code NL-999).

       

      Company nameCompany codeIP-address
      aNL-12356.128.128.5
      aNL-12356.128.128.6
      aNL-12356.128.128.7
      aNL-12356.128.128.8
      bNL-12456.128.128.5
      bNL-12423.100.100.1
      cNL-12599.88.253.253
      dNL-12656.128.128.5
      dNL-126100.73.99.100
      eNL-12799.88.253.253
      eNL-12756.128.128.5
      eNL-127100.73.99.101
      • TomMartens's avatar
        TomMartens
        Icon for Super User rankSuper User
        8 years ago

        Hey,

         

        this will do what you are looking for in the QueryEditor

         

        1. Group your table
          From the Transfor menu select "Group by"
        2. Expand the table in the "dummy" column




          Finally you can create a custom column, that assigns the "Z-company" if the value of the Count column is gt 1 otherwise return the value from the Company code column, like so:
        3. Create custom column
          From the menu "Add column" choose "Custom column"






         

        Hopefully this is what you're looking for, here is a little screenshot of my final table using your sample data:

         

        Regards,
        Tom