Merge 2 tables without creating duplicates with Power Query

If I understand correct, you want to Left Outer join Table 1 and Table 2 on both the Emp Id and Dept Columns.

To do this, hold the control key and click 'Emp ID' in Table 1, 'Emp ID' in Table 2, 'Dept' in Table 1 then 'Dept' in Table 2. You should then see small numbers appearing at on the column headers to show what is being matched to what.

Remember to expand the 'Table2' columns in Power Query editor to show attributes 4,5 and 6.

Is it possible to do it just with the Emp ID? Also I did it with my original data set, it still duplicates the rows.

If you do it with just the EmpID, it will duplicate unless they are matched on a unique basis between the tables. For example, just EmpID 1 will match all the following rows when you expand to show attributes 4,5 and 6:

1 - Finance > 1 - Finance
1 - Finance > 1 - Data
1 - Finance > 1 - Tax
1 - Finance > 1 - Legal
1 - Data > 1 - Finance
1 - Data > 1 - Data
1 - Data > 1 - Tax
1 - Data > 1 - Legal
1 - Tax > 1 - Finance
1 - Tax > 1 - Data
1 - Tax > 1 - Tax
1 - Tax > 1 - Legal

From your example, it looks like you are expecting only the following matches though

1 - Finance > 1 - Finance
1 - Data > 1 - Data
1 - Tax > 1 - Tax

In which case, you need to match on two columns to make them match on a unique basis. Using my approach on your sample data it matched your expected output exactly. Perhaps there are other differences in your original dataset?

Please try

LeftOuter

PHP Code: 

let
    Source = Table.NestedJoin(Table1, {"Emp Id", "Dept"},Table2, {"Emp Id", "Dept"}, "T", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Source, "T",  List.Skip(List.Union(List.Transform(Source[T],Table.ColumnNames)),2))
in
    Expanded 


or FullOuter

PHP Code: 

let
    Source = Table.Combine({Table1,Table2}),
    Unpivoted = Table.UnpivotOtherColumns(Source, {"Emp Id", "Dept"}, "Attribute", "Value"),
    Pivoted = Table.Pivot(Unpivoted, List.Distinct(Unpivoted[Attribute]), "Attribute", "Value", List.Sum)
in
    Pivoted