Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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"
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
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
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 ?
@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,
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
@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/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |