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
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
Want To Learn Power BI
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
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors