Merge 2 tables, please help

[laurent_rio]

Hi,

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

Employee ID

1

5

11

12

15

2nd table = Employee Status

Employee ID	Employee Status
1	Active
3	Terminated
4	Terminated
6	Terminated

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 ID	Employee Status
1	Active
3	Terminated
4	Terminated
5
6	Terminated
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

[amitchandak · Accepted solution]

@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"

[v-yiruan-msft · Accepted solution]

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"

Best Regards

[laurent_rio]

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 ?

[amitchandak · Accepted solution]

@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"

[laurent_rio]

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

[amitchandak]

@laurent_rio , refer to the file attached after signature

[amitchandak]

@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/