Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.