Add rows to table according to grouping in different table

[Gozde]

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

[Zubair_Muhammad]

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

[Zubair_Muhammad]

@Gozde

Please see the attached file

[Gozde]

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

[Zubair_Muhammad]

@Gozde

Hi,

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

Or share your file?

[Gozde]

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

[Zubair_Muhammad]

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

[Zubair_Muhammad · Accepted solution]

@Gozde

Please see the revised file attached

[Gozde]

Yes, changing generate to generateall solve the issue!

Thank you very much,

Gozde

[Zubair_Muhammad]

@Gozde

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

[Gozde]

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

[Zubair_Muhammad]

@Gozde

Did you see the revised file I sent?

[Gozde]

Thanks a lot!

I found my syntax error 🙂