How to match and merge two tables to one new table ?

[MgLina]

Hi Forum, 

I have tried Append and Merge in Power query, but it doesn't work for me, beacause, in my case, it's not just add the rows from table 2 to table 1, or not add the columns from table 2 to table 1. I need one solution that can match the rows for those values of columns matched and keep the rows not matched. Like the example i show here.

Table 1

Part	Step	Date projected	Date final projected	Date final
AAA	S1	04/04/2021	06/04/2021	05/04/2021
AAA	S2	17/04/2021	18/04/2021
BBB	S2	06/04/2021	10/04/2021

Table 2 (update of Table 1)

Part	Step	Date projected	Date final projected	Date final
AAA	S2	17/04/2021	18/04/2021	17/04/2021
BBB	S2	06/04/2021	10/04/2021
CCC	S1	20/04/2021	22/04/2021

Table 2 has one row removed and some udaptes (color in blue) compare to Table 1.

So I want a solution (DAX or Query) that can match and merge these 2 tables to one new table like below:

Part	Step	Date projected	Date final projected	Date final
AAA	S1	04/04/2021	06/04/2021	05/04/2021
AAA	S2	17/04/2021	18/04/2021	17/04/2021
BBB	S2	06/04/2021	10/04/2021
CCC	S1	20/04/2021	22/04/2021

Please, anyone has an idea ? Thank you a lot!

[MgLina · Accepted solution]

Thank you for any help in the forum, please find the solution i use below, it's not the best one, but il works.

1- I create a new table using Union (Table 1; Table 2) and Groupyby

Table =
Var UnionTable=UNION(SELECTCOLUMNS('TABLE1';"Client";'TABLE1'[Client];"PartNo";"TABLE1"[PartNo];"Titre";'TABLE1'[Titre];"TYPE";'TABLE1'[TYPE];"Start Date projected";'TABLE1'[StartDate];"End Date projected";'TABLE1'[EndDate];"Real End Date";'TABLE1'[RealEndDate];"Projet Name";'TABLE1'[ProjName]);SELECTCOLUMNS('TABLE2';"Client";'TABLE2'[Client];"PartNo";"TABLE2"[PartNo];"Titre";'TABLE2'[Titre];"TYPE";'TABLE2'[TYPE];"Start Date projected";'TABLE2'[StartDate];"End Date projected";'TABLE2'[EndDate];"Real End Date";'TABLE2'[RealEndDate];"Projet Name";'TABLE2'[ProjName]))
Return
Groupby(UnionTable;[Client];[PartNo];[Titre];[TYPE];[Start Date projected];[End Date projected];[Real End Date];[Projet Name])

2- I create a new table using Groupyby, but in this time, i don't group the 'Real End Date' column.

TableNew =
Groupby(Table;'Table'[Client];'Table'[PartNo];'Table'[Titre];'Table'[TYPE];'Table'[Start Date projected];'Table'[End Date projected];'Table'[Projet Name])

3-In TableNew, i add new Column using the code below:

EndDateFinal = calculate(FIRSTNONBLANK('Table'[Real End Date];1);FILTER(all('Table');'Table New'[PartNo]='Table'[PartNo]&&'TableNew'[Titre]='Table'[Titre]&&'TableNew'[TYPE]='Table'[TYPE]))

[CNENFRNL]

@MgLina 

PQ solution

DAX solution

[MgLina]

Hi, could i know what is in Progress file ? Or could i have a screenshot of your solution, I really need your solution.

I was tried to use the UNION and Groupby in DAX like below, but i get the row duplicated when the column 'Real End Date' doesn't match between two tables. 

Table =
Var UnionTable=UNION(SELECTCOLUMNS('TABLE1';"Client";'TABLE1'[Client];"PartNo";"TABLE1"[PartNo];"Titre";'TABLE1'[Titre];"TYPE";'TABLE1'[TYPE];"Start Date projected";'TABLE1'[StartDate];"End Date projected";'TABLE1'[EndDate];"Real End Date";'TABLE1'[RealEndDate];"Projet Name";'TABLE1'[ProjName]);SELECTCOLUMNS('TABLE2';"Client";'TABLE2'[Client];"PartNo";"TABLE2"[PartNo];"Titre";'TABLE2'[Titre];"TYPE";'TABLE2'[TYPE];"Start Date projected";'TABLE2'[StartDate];"End Date projected";'TABLE2'[EndDate];"Real End Date";'TABLE2'[RealEndDate];"Projet Name";'TABLE2'[ProjName]))
Return
Groupby(UnionTable;[Client];[PartNo];[Titre];[TYPE];[Start Date projected];[End Date projected];[Real End Date];[Projet Name])

 Thanks a lot!

[MgLina]

Hi @CNENFRNL , Thank you a lot for the solution, but i cannot open the file attached, i got a message said that my version is not compatible 😥.