Forum Discussion
n:n Relationship
I have a problem with n:n Relationships. The proposed solution from this thread doesnt hep me, but it's a similar one. Fictitious example: There are Table 1 with actors and their shows. In Table 2 are the shows and target groups. Both contain columns with double values so i can't connect it. I want to get a table with Actors and their target groups. That's why i create a link table between both as it was proposed by this thread. But it doesn't work.
Is there a way to get actors and target groups in the same table?
Greetings
Tobi
Tobi,
You may also add a calculated table.
Table = GENERATEALL ( Table1, SELECTCOLUMNS ( FILTER ( Table2, Table2[Show] = Table1[Show] ), "Target Group", Table2[Target Group] ) )
5 Replies
Tobi,
You may also add a calculated table.
Table = GENERATEALL ( Table1, SELECTCOLUMNS ( FILTER ( Table2, Table2[Show] = Table1[Show] ), "Target Group", Table2[Target Group] ) )Great, it works. Thank you very much. :smileyvery-happy:
In fact, your upper 2 tables are the link tables.
This works:
Thanks, I see. But is there a way to get Actors and TargetGroups into the same table? It doesn't seem to work that way. I get an error.
Hm, me too.
In the linked thread it is mentioned that you can only combine the data if 1 end is summarized.
That would be e.g. a list of actors with counts of target groups.Plan B would be to merge the tables in Power Query.
Example below with full outer join (you might want to opt for a lef outer join or an inner join).
Because of the full outer join I replaced nulls by blanks, so all values will show up in the visual.
Query Actor_TargetGroup:
let Source = Table.NestedJoin(Actor_Show,{"Show"},Show_TargetGroup,{"Show"},"Show_TargetGroup",JoinKind.FullOuter), #"Expanded Show_TargetGroup" = Table.ExpandTableColumn(Source, "Show_TargetGroup", {"Target Group"}, {"Target Group"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Show_TargetGroup",{"Show"}), #"Replaced Value" = Table.ReplaceValue(#"Removed Columns",null,"",Replacer.ReplaceValue,{"Actor", "Target Group"}) in #"Replaced Value"
