Merge columns in power query

[yarlachiru]

i have merged two tables in power query , my new table has employee name, emp id, work order number 1 , work order description 1, actual days, work order number 12 , work order description 12, Oct-23, Nov-23, Dec-23 plan.
here my work order 1 and work order 12 both columns are same
work order description 1 , work order description 12 columns are also same.
I want to create a new table merge these data together and delete blank rows.
In my sample sheet expected result is shown.
I want to achieve this in power query or fully automated table in excel so that in the future weeks i can simply replace the data and refresh

[CK76]

I'd recommend including source data in the workbook. As is, it is difficult to see where you went wrong in your merge and append operation.
As things are currently structured, you will need to split table into multiple and perform joins to get the result you are after.
Ex:
Use Table.SelectColumns to extract out Employee, Emp ID, Work order Number 1. Another to extract Employee, Emp ID, Work Order Number 12.
Rename columns to be exact match and append queries and remove duplicates.
Referencing starting table, drop every column after Days column, and filter out null. Then Group by and sum days.
Do the same but this time drop Work order Number 1 to Days columns. Then Group by and sum Oct - Mar columns.

Merge queries back into Append query.

See attached.

Though, this sort of transformation should be avoided in Power Query. You should load data to data model (using two fact tables, and dimension tables to relate them).

[yarlachiru]

Thank you CK76