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 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!
Solved! Go to 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]))
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]))
PQ solution
DAX solution
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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!
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 😥.
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 |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |