cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MgLina
Frequent Visitor

Union two tables and no duplicate the rows when date updated

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       
ClientPartNoTitreTYPEStart Date projectedEnd Date projectedReal End DateProjet Name
ClientXXAAAATool ProveNPI09/03/202122/03/202121 mars 21FA1
ClientXXAAAAInspectionNPI09/05/202122/05/2021 FA1
ClientYYBBBBMethodesREV09/04/202122/04/2021 FA1

 

TABLE 2       
ClientPartNoTitreTYPEStart Date projectedEnd Date projectedReal End DateProjet Name
ClientXXAAAAInspectionNPI09/05/202122/05/202123 mai 21FA1
ClientYYBBBBMethodesREV09/04/202122/04/2021 FA1
ClientXXCCCCInspectionNPI10/04/202112/04/2021 FA2

 

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

ClientPartNoTitreTYPEStart Date projectedEnd Date projectedReal End DateProjet Name
ClientXXAAAATool ProveNPI09/03/202122/03/202121 mars 21FA1
ClientXXAAAAInspectionNPI09/05/202122/05/202123 mai 21FA1
ClientYYBBBBMethodesREV09/04/202122/04/2021 FA1
ClientXXCCCCInspectionNPI10/04/202112/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.

ClientPartNoTitreTYPEStart Date projectedEnd Date projectedReal End DateProjet Name
ClientXXAAAATool ProveNPI09/03/202122/03/202121 mars 21FA1
ClientXXAAAAInspectionNPI09/05/202122/05/2021 FA1
ClientXXAAAAInspectionNPI09/05/202122/05/202123 mai 21FA1
ClientYYBBBBMethodesREV09/04/202122/04/2021 FA1
ClientXXCCCCInspectionNPI10/04/202112/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!

1 ACCEPTED SOLUTION
MgLina
Frequent Visitor
5 REPLIES 5
MgLina
Frequent Visitor
daxer
Solution Sage
Solution Sage

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
Frequent Visitor

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

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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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.

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors