Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi,
I would like to add rows to one table ( or combine two tables) according to group number from another table.
These are what I have:
Table 1 | |
Name | Part |
A | x1 |
B | y1 |
B | x1 |
Table 2 | |
Group | Part |
1 | x1 |
1 | x2 |
2 | y1 |
2 | y2 |
This is what I am looking for
Final Table | ||
Name | Part | Group |
A | x1 | 1 |
A | x2 | 1 |
B | y1 | 2 |
B | y2 | 2 |
B | x1 | 1 |
B | x2 | 1 |
I already tried merge/append or DAX code (Union or OuterJoin) and not succeed.
Do you have any recommendation? 🙂
Thank you!
Gozde
Solved! Go to Solution.
Please see the revised file attached
Hi @Gozde
Please try this calculated table
New Table = VAR Mytable1 = ADDCOLUMNS ( Table1, "My Group", CALCULATE ( FIRSTNONBLANK ( Table2[Group], 1 ) ) ) VAR JoinTables = GENERATE ( SELECTCOLUMNS ( mytable1, "Name", [Name], "My Group", [My Group] ), FILTER ( Table2, Table2[Group] = [My Group] ) ) RETURN SELECTCOLUMNS ( JoinTables, "Name", [Name], "Group", [Group], "Part", [Part] )
Please see the attached file
Thanks for quick response. It helped me alot.
It is working in the example code. But, not in my real big data, becauase I have some parts in Table 1 which are missing in Table 2 (not have the group). How can I keep these parts? What should I change in code?
Thank you 🙂
Gozde
Hi,
Could you give me some data and expected results to work with?
Or share your file?
Yes, definetely!
Table 1 | |
Name | Part |
A | x1 |
A | z1 |
B | y1 |
B | x1 |
C | k1 |
Table 2 | |
Group | Part |
1 | x1 |
1 | x2 |
2 | y1 |
2 | y2 |
This is my expectation:
Final Table | ||
Name | Part | Group |
A | x1 | 1 |
A | x2 | 1 |
B | y1 | 2 |
B | y2 | 2 |
B | x1 | 1 |
B | x2 | 1 |
A | z1 | |
C | k1 |
Thank you again!
Regards,
Gozde
Try with this code
New Table = VAR Mytable1 = ADDCOLUMNS ( Table1, "My Group", CALCULATE ( FIRSTNONBLANK ( Table2[Group], 1 ) ) ) VAR JoinTables = ADDCOLUMNS ( GENERATEALL ( SELECTCOLUMNS ( mytable1, "Name", [Name], "My Group", [My Group], "My Part", [Part] ), FILTER ( Table2, Table2[Group] = [My Group] ) ), "Final Part", IF ( ISBLANK ( [Part] ), [My Part], [Part] ) ) RETURN SELECTCOLUMNS ( JoinTables, "Name", [Name], "Group", [Group], "Part", [Final Part] )
Please see the revised file attached
Yes, changing generate to generateall solve the issue!
Thank you very much,
Gozde
Actually in addition to GenerateAll there was a subtle issue with Parts Column as well
Nope, When I add your last code, again parts without group number are disappering at final table.
I tried your code with the example:
This is the output.
Name | Part | Group |
A | x1 | 2 |
A | x2 | 2 |
B | x1 | 2 |
B | x2 | 2 |
C | x1 | 1 |
C | x2 | 1 |
Did you see the revised file I sent?
Thanks a lot!
I found my syntax error 🙂
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
88 | |
86 | |
82 | |
65 | |
49 |
User | Count |
---|---|
138 | |
110 | |
104 | |
66 | |
64 |