cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
laurent_rio
Frequent Visitor

Merge 2 tables, please help

Hi,

 

I need to merge my 2 tables 
1st tables = Employee List

Employee ID
1
5
11
12
15


2nd table = Employee Status

Employee IDEmployee Status
1Active
3Terminated
4Terminated
6Terminated

Since Employee ID in table 1 & 2 not sync (  some ID exist in table 1 and some in table 2 only) , i want to combine this 2 table into 1 table 

Employee IDEmployee Status
1Active
3Terminated
4Terminated
5 
6Terminated
11 
12 
15 

 

I try use merge query with mathing column is employee ID on both table and then choose Left Outer as join kind but it will not show me data from table 2 (ID 3,4,6). What should i choose to get the table as i want ?

Please advice

 

2 ACCEPTED SOLUTIONS

@laurent_rio , Have you checked my file?

I added a new column and combined ids.

 

let
    Source = Table.NestedJoin(Table1, {"Employee ID"}, #"Table (2)", {"Employee ID"}, "Table (2)", JoinKind.FullOuter),
    #"Expanded Table (2)" = Table.ExpandTableColumn(Source, "Table (2)", {"Employee ID", "Employee Status"}, {"Table (2).Employee ID", "Table (2).Employee Status"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table (2)", "ID", each if [Employee ID] =null then [#"Table (2).Employee ID"] else [Employee ID]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Employee ID", "Table (2).Employee ID"})
in
    #"Removed Columns"

 



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

Proud to be a Super User!

View solution in original post

yingyinr
Community Support
Community Support

Hi @laurent_rio ,

If you still want to keep the original tables(Employee List and Employee Status), you can add new blank query with the following codes in Advanced Editor to get the combined data.

let
    Source = Table.Combine({#"Employee Status", #"Employee List"}),
    #"Removed Duplicates" = Table.Distinct(Source, {"Employee ID"})
in
    #"Removed Duplicates"

yingyinr_0-1618297541712.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
yingyinr
Community Support
Community Support

Hi @laurent_rio ,

If you still want to keep the original tables(Employee List and Employee Status), you can add new blank query with the following codes in Advanced Editor to get the combined data.

let
    Source = Table.Combine({#"Employee Status", #"Employee List"}),
    #"Removed Duplicates" = Table.Distinct(Source, {"Employee ID"})
in
    #"Removed Duplicates"

yingyinr_0-1618297541712.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

laurent_rio
Frequent Visitor

Hi new table using full outer will be like this, the employee ID from table 2 is return as null. Any way to display employee ID in table 2 ?

 

 

Capture.JPG

@laurent_rio , Have you checked my file?

I added a new column and combined ids.

 

let
    Source = Table.NestedJoin(Table1, {"Employee ID"}, #"Table (2)", {"Employee ID"}, "Table (2)", JoinKind.FullOuter),
    #"Expanded Table (2)" = Table.ExpandTableColumn(Source, "Table (2)", {"Employee ID", "Employee Status"}, {"Table (2).Employee ID", "Table (2).Employee Status"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table (2)", "ID", each if [Employee ID] =null then [#"Table (2).Employee ID"] else [Employee ID]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Employee ID", "Table (2).Employee ID"})
in
    #"Removed Columns"

 



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

Proud to be a Super User!

View solution in original post

laurent_rio
Frequent Visitor

Hi,

 

I try with full outer but when i try to expand and show the employee status. The employee ID that not in table 1 is dissapear

@laurent_rio , refer to the file attached after signature

 



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

Proud to be a Super User!

amitchandak
Super User IV
Super User IV

@laurent_rio , Merge in power query with a full outer option

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

 

in DAx you can use cross join and filter

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/



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

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors