cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shefalinishad11
Helper I
Helper I

Create unique table from two tables

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
IDName
Emp1Emp1name
Emp1Emp1name
Emp3Emp3name
Emp5Emp5name
Emp5Emp5name
Table 2
IDName
Emp1Emp1name
Emp1Emp1name
Emp3Emp3name
Emp3Emp3name
Emp3Emp3name

 

New Unique Table
IDName
Emp1Emp1name
Emp2Emp2name
Emp3Emp3name
Emp4Emp4name
Emp5Emp5name
1 ACCEPTED SOLUTION
PoojaDarbhe
Resolver I
Resolver I

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

View solution in original post

4 REPLIES 4
v-xiaotang
Community Support
Community Support

Hi @shefalinishad11 

 Have you solved this problem?

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

1. hit Append  Queries as New

v-xiaotang_0-1620812857213.png

2.remove unused columns

v-xiaotang_1-1620812857218.png

3.remove duplicates

v-xiaotang_2-1620812857222.png

Result:

v-xiaotang_3-1620812857223.png

 

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
Resolver I
Resolver I

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

View solution in original post

AlB
Super User
Super User

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"

 

SU18_powerbi_badge

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
Super User
Super User

@shefalinishad11 , Try like

 

distinct(union(Table1, Table2))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.