Move duplicates from one column to another column

[Anonymous]

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]

Hey,

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

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]

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 name	Company code	IP-address
a	NL-123	56.128.128.5
a	NL-123	56.128.128.6
a	NL-123	56.128.128.7
a	NL-123	56.128.128.8
b	NL-124	56.128.128.5
b	NL-124	23.100.100.1
c	NL-125	99.88.253.253
d	NL-126	56.128.128.5
d	NL-126	100.73.99.100
e	NL-127	99.88.253.253
e	NL-127	56.128.128.5
e	NL-127	100.73.99.101

[TomMartens · Accepted solution]

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

[Anonymous]

Many thanks Tom!

[TomMartens]

Hey,

maybe this gets you started to write some DAX code:

https://docs.microsoft.com/en-us/power-bi/desktop-quickstart-learn-dax-basics

If I understand you correctly you want a column that uses the same "company code" for all the ip's that are used by more than one company, and "reuses" the original company code if the ip address is not shared?

Regards,

Tom  

[Anonymous]

Hi,

The IP-address in this table is the key to connect to the logfile in which usage of sources is monitored.

Since the IP-address in the logfile could have multiple occurences, we have to make sure that the IP-address in this table is unique (otherwise you can't create a relationship between this table and the logfile). Another reason if of course that we can't pinpoint specific usage to a certain company if the IP-address is shared.

So the rows with a unique IP-address should stay where they are.

And indeed I want that all duplicate IP-addresses have the same company name and code (multiple rows since there's 1 IP-address per row) and are deduplicated (since I want only unique IP-addresses in the table).