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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.