Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
5 REPLIES 5
MgLina
Frequent Visitor
Anonymous
Not applicable

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.

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 an 8 times 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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors