Merge two tables in Power query

[ChirSidh]

i have two tables in power query , first one
Employee Emp ID Merged Work order numbers Merged Work order description Actual Time Nov-2023 Dec-2023 etc

second one is
Employee Emp ID CTC Owner Work Order Number Work Order Description etc

what i want to do is when i load this information in to pivot table in excel, i want to bring CTC owner column to first table, how do i do this?.

EMP ID is common between two tables, Merged work order numbers and Work order number are also common, however all the numbers in Merged worked numbers are not available in Work order number column, but all the work order number column numbers are available in Merged work order numbers column.

I want to do this in power query only because there are many preceding tables to these two tables. Can anyone help me please?

https://we.tl/t-knMz7eYuPz

[v-stephen-msft]

Hi @ChirSidh ,

When you use Merge Queries:

1.Click on the first table in the Queries pane to select it.

2.Go to the 'Home' tab in the Power Query Editor and click on 'Merge Queries'.

3.In the Merge window, select the second table from the dropdown. - Click on the 'Emp ID' column in the first table and then click on the 'Emp ID' column in the second table to set them as the key columns for the merge.

4.Since 'Merged Work Order Numbers' and 'Work Order Number' are also common, hold the 'Ctrl' key and click on these columns as well to add them to the key columns.

5.Choose the join kind that suits your needs (likely 'Left Outer' as you want all from the first table and matching from the second). - Click 'OK'.

Then expand the Merged Column. The merged column is a new column named 'NewColumn' (or similar) that contains the merged table.

Best Regards,

Stephen Tao

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

[ChirSidh]

let
Source1 = #"Append2-Nov-23 (2)",
Source2 = #"Merge2(2)-Nov-23",
MergedTable = Table.NestedJoin(Source1, {"Emp ID", "Merged Work order numbers"}, Source2, {"Emp ID", "Work Order Number"}, "MergedData"),
ExpandedTable = Table.ExpandTableColumn(MergedTable, "MergedData", {"CTC Owner"})
in
ExpandedTable


i have used this, but this is only partially working, in this formula only EMP ID is common between two tables, work order numbers some available in first table are not there in second table, but all the work order numbers in second table are available in the first table. CTC owner is showing for only those work orders which are there in the first table correctly, for others wrong owner name is showing