March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch 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 🙂
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
167 | |
149 | |
93 | |
72 | |
58 |