Creating a table from another table

[Anonymous]

I need to create a new table based on the actual one (see example):

serial	email
1234	name@actualcompany.com
1234	name@oldcompany.com
	name@company.com
4321	name@actualcompany.com

The new one should not have duplicated serial number (we need to keep the ones with email @actualcompany.com ) and remove the ones with blank serial. So, the new table should be:

serial	email
1234	name@actualcompany.com
4321	name@actualcompany.com

[amitchandak · Accepted solution]

In Edit Query mode, filter for actualcompany.com and Non blank Serial.

[freder1ck]

Yes to what @amitchandak posted. Also, to make a new table right click on the query and select Reference. I presume your data is a bit more complex than your example, so remove duplicates on the serial column (or the serial column and the actual company email).

I'm not sure of the context so I have a few questions...

• If you have many companies, how do you know which is the actual company email?
• Are you using a slowly changing dimension somewhere?
• Do you need this as a physical table in your database, or do you just need a virtual table for a SUMX in DAX or something? 

[Anonymous]

@freder1ck follow:

• If you have many companies, how do you know which is the actual company email? For those with same serial number, I need to keep those with actual email domain.
• Are you using a slowly changing dimension somewhere? No
• Do you need this as a physical table in your database, or do you just need a virtual table for a SUMX in DAX or something? After being able to do this clean up, I plan to add a column of Sales Target for each user. Since I´m using SF object, which I can´t change, I believe I´ll have to create a new physical table, with these unique users.