cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

1 ACCEPTED SOLUTION
Community Champion

@Gozde

Please see the revised file attached

Regards
Zubair

12 REPLIES 12
Community Champion

Hi @Gozde

```New Table =
VAR Mytable1 =
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

Community Champion

@Gozde

Regards
Zubair

Helper I

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

Community Champion

@Gozde

Hi,

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

Regards
Zubair

Helper I

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

Community Champion

@Gozde

Try with this code

```New Table =
VAR Mytable1 =
Table1,
"My Group", CALCULATE ( FIRSTNONBLANK ( Table2[Group], 1 ) )
)
VAR JoinTables =
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

Community Champion

@Gozde

Please see the revised file attached

Regards
Zubair

Helper I

Yes, changing generate to generateall solve the issue!

Thank you very much,

Gozde

Community Champion

@Gozde

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

Regards
Zubair

Helper I

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
Community Champion

@Gozde

Did you see the revised file I sent?

Regards
Zubair

Helper I

Thanks a lot!

I found my syntax error 🙂

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors