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

Get 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

Reply
Gozde
Helper I
Helper I

Add rows to table according to grouping in different table

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
NamePart
Ax1
By1
Bx1

 

Table 2
GroupPart 
1x1
1x2
2y1
2y2

 

This is what I am looking for

Final Table  
NamePartGroup
Ax11
Ax21
By12
By22
Bx11
Bx21

 

 

I already tried merge/append or DAX code (Union or OuterJoin) and not succeed.

 

Do you have any recommendation? 🙂

 

Thank you!

Gozde

1 ACCEPTED SOLUTION

@Gozde

 

Please see the revised file attached

 

addrows1.png


Regards
Zubair

Please try my custom visuals

View solution in original post

12 REPLIES 12
Zubair_Muhammad
Community Champion
Community Champion

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

Regards
Zubair

Please try my custom visuals

@Gozde

 

Please see the attached file

 

Addrows.png


Regards
Zubair

Please try my custom visuals

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

@Gozde

 

Hi,

Could you give me some data and expected results to work with?

 

Or share your file?


Regards
Zubair

Please try my custom visuals

Yes, definetely!

 

Table 1
NamePart
Ax1
Az1
By1
Bx1
Ck1

 

Table 2
GroupPart 
1x1
1x2
2y1
2y2

 

This is my expectation:

Final Table  
NamePartGroup
Ax11
Ax21
By12
By22
Bx11
Bx21
Az1 
Ck1 

 

 

Thank you again!

Regards,

Gozde

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

Regards
Zubair

Please try my custom visuals

@Gozde

 

Please see the revised file attached

 

addrows1.png


Regards
Zubair

Please try my custom visuals

Yes, changing generate to generateall solve the issue!

 

Thank you very much,

Gozde

@Gozde

 

Actually in addition to GenerateAll there was a subtle issue with Parts Column as well Smiley Tongue


Regards
Zubair

Please try my custom visuals

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.

NamePartGroup
Ax12
Ax22
Bx12
Bx22
Cx11
Cx21Capture-PowerBI.PNG

@Gozde

 

Did you see the revised file I sent?

 

 

 

 


Regards
Zubair

Please try my custom visuals

Thanks a lot!

 

I found my syntax error 🙂

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.