Forum Discussion
Helper IMerge 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 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
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"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
6 Replies
- amitchandak
Super User
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/
laurent_rioHi,
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
- 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
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"
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