Create unique table from two tables

[shefalinishad11]

Hi Team,

I am trying to create a new unique employee table with column, name id, name, rank, country. The two other tables are table1 & table2, both tables have the same 4 fields with the other 15 fields.  The main concept is, these two tables get a new name added on multiple occasions, so I want to create a new table (employee) that acts as the main table for the whole report.

Table 1
ID	Name
Emp1	Emp1name
Emp1	Emp1name
Emp3	Emp3name
Emp5	Emp5name
Emp5	Emp5name

Table 2
ID	Name
Emp1	Emp1name
Emp1	Emp1name
Emp3	Emp3name
Emp3	Emp3name
Emp3	Emp3name

New Unique Table
ID	Name
Emp1	Emp1name
Emp2	Emp2name
Emp3	Emp3name
Emp4	Emp4name
Emp5	Emp5name

[v-xiaotang]

Hi @shefalinishad11 

 Have you solved this problem?

I create a sample, and you can take this for reference.

1. hit Append  Queries as New

2.remove unused columns

3.remove duplicates

Result:

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[PoojaDarbhe · Accepted solution]

Hi @shefalinishad11 ,

You can create a measure like below

testcustomTab = DISTINCT(UNION(SELECTCOLUMNS(table1, "ID", table1[ID], "Name", table1[name]),SELECTCOLUMNS(table2, "ID", table2[id], "Name", table2[name])))

Let me know in case of any help.

Best regards,

Pooja Darbhe

[AlB]

Hi @shefalinishad11 

You can do this best in PQ.

Simply append both tables and then remove duplicates. See it all at work in the attached file.

let
    Source = Table.Combine({Table1, Table2}),
    #"Removed Duplicates" = Table.Distinct(Source)
in
    #"Removed Duplicates"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[amitchandak]

@shefalinishad11 , Try like

distinct(union(Table1, Table2))