Solve Many to Many Joins Using Bridge Tables - Monkey Shorts Episode 038

If you've ever tried to join two tables in your Excel Power Pivot Data Model (or your Power BI Data Model), and been blocked by or warned about duplicate values, a Bridge Table may be just what you need. In this video Ken shows his preferred recipe for creating bridge tables, ensuring that you'll never have an orphaned record in your end results.

[gvwur]

Thanks for this! Saved me some pain in PowerBI, as I could not use many-to-many in my version of the software.

[elaf]

Hi Ken - Love all your Monkey short videos format & samples. Keep going it's always great content, very enjoyable + useful & I always thrill to follow your next one.

I usually share & follow your methodology approach to build my DataModels : to split queries between RawData, Stagging & DataModel ones.
But in that particular case, I would prefer to choose a more concise approach to get same result. : Perhaps then more in a Miguel's than Ken's way so ... :=)

To build query for the new [Category] table to add : 1 other solution can be done with a "single" line of M-Code. Using List functions
Source = Table.FromList( List.Distinct( List.Combine({Sales[Categorie], Budget[Categorie]})), Splitter.SplitByNothing(), {"Category"}, null, ExtraValues.Error)

+ "FORMAT" it in AdvanceEditor on separated items. For an easier Reading & Understanding.

But of course this would perhaps be a little less easy, educative & progressive way to learn it for people watching your video.
+ there's not always a need to try to reduce the steps of solution you're building. But it can in some case optimize its performance as well.

[mattsimon]

definitely a topic we struggle with - I think I have to watch this vid a couple time to understand it.

[tomharvey]

How do you do a Bridge table when each table had different granularity? For example, I have locations in my people data it's Building (Location and in my recruiting system it's Combining them into the data model on Location ID doesn't work because it does not appear in my recruiting system nor can I use city because city is not unique in my people data (we have multiple locations in each city).

[meteora]

Thx. Do you pull the category from the bridge table or can you use the category from either fact table?

[g.janardhanreddyjanardhan]

Hello. Want coun rows of table visual from multiple tables