Union two tables and no duplicate the rows when date updated

[MgLina]

Hi All,

I am new to PowerBi, I need your help please.

I have two tables (TABLE1, TABLE2) that i want to union:

TABLE 1
Client	PartNo	Titre	TYPE	Start Date projected	End Date projected	Real End Date	Projet Name
ClientXX	AAAA	Tool Prove	NPI	09/03/2021	22/03/2021	21 mars 21	FA1
ClientXX	AAAA	Inspection	NPI	09/05/2021	22/05/2021		FA1
ClientYY	BBBB	Methodes	REV	09/04/2021	22/04/2021		FA1

TABLE 2
Client	PartNo	Titre	TYPE	Start Date projected	End Date projected	Real End Date	Projet Name
ClientXX	AAAA	Inspection	NPI	09/05/2021	22/05/2021	23 mai 21	FA1
ClientYY	BBBB	Methodes	REV	09/04/2021	22/04/2021		FA1
ClientXX	CCCC	Inspection	NPI	10/04/2021	12/04/2021		FA2

The result I want after union (TABLE1; TABLE2) is like below:

Client	PartNo	Titre	TYPE	Start Date projected	End Date projected	Real End Date	Projet Name
ClientXX	AAAA	Tool Prove	NPI	09/03/2021	22/03/2021	21 mars 21	FA1
ClientXX	AAAA	Inspection	NPI	09/05/2021	22/05/2021	23 mai 21	FA1
ClientYY	BBBB	Methodes	REV	09/04/2021	22/04/2021		FA1
ClientXX	CCCC	Inspection	NPI	10/04/2021	12/04/2021		FA2

But with DAX, i obtain the table below which keeps the row of TABLE1 and the row with date updated in column "Real End Date" of TABLE2 instead of keeping just row of TABLE2.

Client	PartNo	Titre	TYPE	Start Date projected	End Date projected	Real End Date	Projet Name
ClientXX	AAAA	Tool Prove	NPI	09/03/2021	22/03/2021	21 mars 21	FA1
ClientXX	AAAA	Inspection	NPI	09/05/2021	22/05/2021		FA1
ClientXX	AAAA	Inspection	NPI	09/05/2021	22/05/2021	23 mai 21	FA1
ClientYY	BBBB	Methodes	REV	09/04/2021	22/04/2021		FA1
ClientXX	CCCC	Inspection	NPI	10/04/2021	12/04/2021		FA2

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])

Anyone knows how to fix this problem ?

Thanks!

[MgLina · Accepted solution]

See the solution in https://community.powerbi.com/t5/Power-Query/How-to-match-and-merge-two-tables-to-one-new-table/m-p/... 

[Anonymous]

Hi @MgLina 

DAX is not a tool for data mashup; it's a language for data analysis. M is such a language. Please use Power Query to union two tables. Trying to sqeeze a square peg into a round hole is not the best idea.

[MgLina]

Thank you Daxer !, i will try to union it in power query.

[MattAllington]

My guess is that your datamodel is not optimal. I can't think of many usecases where is is good practice to load 2 tables and then Union them. Have you considered appending the tables using power query before load?  

Union in DAX is the same as Unionall in SQL. If you want the Union equivalent you need to use 

DISTINCT(UNION(table1,table2))

[MgLina]

Hi MattAllington, non, i have just transform the tables in power query. Not append the tables. I will try it.  Thank you for your suggestion.