Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
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))
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.
User | Count |
---|---|
47 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |