MSPTDA 28: Build Power Query Bridge Table in Power BI & Power Pivot for Many To Many Relationship

[@excelisfun]

Topics: 1. (00:23) Introduction & look at Data Modeling Problem 2. (01:00) Fundamental Problem with a Many-To-Many Relationship 3. (02:05) What is a Many-To-Many Relationship? 4. (03:25) Start to create Reports and discover the Problem with a Many-To-Many Relationship. 5. (04:52) Build Bridge Table using Excel Power Query 6. (07:32) First look at underlying filtering for a Many-To-Many Relationships 7. (08:36) Three Options with a Bridge Table. 8. (09:02) Build Bridge Table using Power BI Desktop Power Query 9. (10:25) Bi-Directional Filter 10. (10:53) Visual Animation to illustrate how Many-To-Many Relationship works with a Bridge Table. 11. (11:39) Ambiguity in Model with Bi-Directional Filters. 12. (12:19) CROSSFILTER DAX Function example 13. (13:41) Table Filter Example 14. (14:05) Expanded Diagram to “see” how Table Filter works with a Bridge Table and a Many-To-Many Relationships 15. (15:08) Difference between Grand Total Cell formulas for CROSSFILTER and Table Filter 16. (16:10) Cross Tab Report 17. (16:53) Conclusion

[@JeffersonAlvesDadosCriativos]

Amazing video 👏👏👏👏

[@excelisfun]

@@JeffersonAlvesDadosCriativos Glad you like it!

[@IoriYagamiKOF98]

i just want to leave my opinion, i barely do comment any videos, but i think you are one of the best educator when in BI , your videos are so freaking well explained, your examples , you don't just talk and show the demo.... you take a momment to show how the background of the engine works, with your tables examples, how the filter flows throught the arrows ... just amazing.... i hope you never stop... your videos are really the best i found

[@excelisfun]

I am glad that the resources that I post are helpful for you, Erick! I will not stop, especially since I have been posting videos at 11 years and it is just too much fun! The detailed video story takes a lot of time to plan, film, edit and upload - but I would not do it any other way. If these help, please help support the cause by supporting with comments and thumbs ups on each video and a Subscription : ) Good news, Erick, there are many many more videos to come and over 3000+ videos posted for you to search for just the topic you want : )

[@laviedandre]

Table expansion is powerful but it's also one those more abstract DAX concepts (like context transition). Love how you included it and showed the implications side-by-side with the CROSSFILTER approach. Thank you, as always!

[@excelisfun]

You are welcome, Andre! I am happy that the side-by-side action was fun and helpful! Thanks for your support : )

[@GeertDelmulle]

Thanks for the video and the clarification, Mike. As per usual your visuals are the best and a huge help in clarifying how it all works! Other channels -- although professional (apparently) -- do such a lousy job "explaining" how it works (they just talk around the subject), and show nothing to support the explanation. Unbearable for us who are spoiled by your level of teaching. Indeed, the easiest explanation is the best: "in the total row the CROSSFILTER measure does not filter out any records and therefore it gives the overall total, including the count of units for the unmatched record in the dBooks table.

[@excelisfun]

I can not even put into words how much I appreciate you saying, out loud Geert, that the resources I create explain well. I agree that my video story telling, to help reveal "complicated topics as less complicated topics", is mostly unmatched in the world, and this includes all the smartest book writers and bloggers in the world. As I have mentioned before, I just can't seem to get the free resources out to more of the people who want them, people like you who want to take the time to learn well, learn the whys, and then have fun applying the concepts in creative ways to solve problems... If only I new how to market free resources better... Thanks, Geert : )

[@ricos1497]

@@excelisfun Well I'd like to thank Geert. The way Geert explains his thoughts on your videos is so clear, with all the supporting evidence, explaining why he finds them so useful. In fact, Geert, I think your comments of appreciation for Mike are the best of all the comments on youtube! Basically, I agree with Geert. Phenomenal videos Mike. Could you do a video on the many to many relationship between my comments on Geerts comments on your videos?

[@excelisfun]

@@ricos1497 , Thank you Rico S and Geert : ) : )

[@French_Connection]

Geert ... other channels have usually a beautiful girl shown in front of tables, they spend time on showing how to change colors on chart, make things nice but no substance. Here a bald guy actually delivers the goods. What difference.

[@shoeshines2121]

This was an outstanding explanation of how to introduce a bridge table to overcome the limitations of the many-to-many relationship. I really enjoyed the PowerQuery transformation of Unpivoting Other Columns to generate the bridge table so effortlessly. It was also informative to hear that it is preferable to explicitly define the relationship in the DAX calculation by both using CROSSFILTER and the "Expanded Table Concept" thus avoiding the risk of ambiguity in bigger data models. Thank you for the great introduction to many-to-many relationships in Data Modeling.

[@excelisfun]

You are welcome, TP : )

[@French_Connection]

This is magic. UNBELIEVABLE. I watched Ferrari's presentation about sales, Rossi's about time functions. But they gave just formulae. In this presentation, the formula and comparison on worksheet is available. Also, Mike, you saved the best for last. Just throwing full bridge table to filter is amazing. I have to say that even when I was selecting the items in formula by tabs I made some mistakes. Model did not work initially. But with table filter - it did right away. And I understood the hassle it saved me. I may add that you give so much more here than most !!!

[@excelisfun]

I am glad to help you and others, F C : ) My goal for 12 years at YoTube is: Free Excel Education for the World!!!

[@xiaojienan7891]

Sooo appreciate your video, god bless you, help me a lot

[@mueez89]

Great explanation! The best teacher in the world!

[@excelisfun]

Glad you think so!

[@athandapani]

Excellent explanation, it includes all variations I wanted to understand about many to many relationship and visuals in between clarified the doubts. Great work Mike👍👏

[@excelisfun]

Glad the visuals help you to gain the knowledge of how and why, Arun!!!! Please help support this channel with your Sub, and your thumbs up and comment on each video that you watch : )

[@sane7263]

Came here after a long time Man! And this video is still Awesome, Thanks, Mike!

[@excelisfun]

Yes!!! You are welcome! Great to see that you are keeping it Sane with MSPTDA : )

[@clarkwang2945]

Extremely clear explanation on those table connections and this is very helpful to beginners for sure. Thanks Mike.

[@excelisfun]

You are welcome, Clark! There is almost nothing more fun than getting the Data Model just right for your report : )

[@ljubicar1987]

Wow!!! I see some things clearer now! Your videos are comprehensive and cover every important topic! Thumbs up!👍

[@atiqkhan9637]

you are taking me from zero to hero, i thought i should learn power pivot, but i ended up become a fan of power query. thanks mike

[@excelisfun]

Yes, Power Query is the key to everything becasue if the data is no good, everything that follows in no good ; )

[@shadow_gaming_sk]

Thanks Mike u r sharing good knowledge to all👍

[@excelisfun]

Yes, that is my intent : ) Thanks for your support, chinna k!!!!!!

[@mohitmanwani5298]

WOhh!!.. Just opened my mind .....It was really amazing video Mike especially Bridge Table as a filter argument to Calculate to remove unmatched rows from Dim Table...

[@JIMMYLoki1]

You are making me a star, I really appreciate

[@excelisfun]

Yes, I love to hear that, Loki!!! You aer a Star!!!! Do NOT forget your duty, since I am helping to make you a star, for you to help get these free resources to more humans in the world with your comments and thumbs ups on each video that you watch : )

[@zt.5677]

I have not visited ExcellsFun for some time. It is still rock hard excellent. It is good to know that it is still being updated and managed. Thank you, Mike!

[@excelisfun]

Are you subscribed, so you know when videos are posted? You are welcome for the material, M. SZ.

[@SyedMuzammilMahasanShahi]

Beautiful work. Thanks Mike for the share.

[@excelisfun]

Yes, Sir!!! I am happy to share the fun : )

[@georgetosounidis5545]

Thank you so much for this, it was simply amazing!!! Congratulations for your excellent work!!

[@excelisfun]

You are welcome so much, George! Thanks for your comment, Thumbs up and your Sub : )

[@chrism9037]

Powerful stuff Mike! Fantastic!!

[@excelisfun]

Thanks, Chris!!! I am glad that you get this power!!!!

[@katerina6495]

Thank you for the great video Mike and great explanation as always

[@excelisfun]

You are welcome, Katerina!!!

[@mohamedchakroun4973]

Thanks Mike millions thanks for the effort of teaching us secrets tricks of Excel

[@wayneedmondson1065]

Hi Mike.. another excellent video. Thanks for the lesson on Bridge Tables and how to make them work. Thumbs up!

[@jeffkasavan93]

Thanks for another stellar video - it's cool to see how to do these data evaluations in 3 (or more) ways.

[@excelisfun]

Yes, always multiple ways to have fun with Excel : )

[@johnborg6005]

Thanks Mike. Geert comments are so true. I can't thank you enough for all the material I get from you. :)

[@nimrodzik1]

Thank You Mike. It was great video :)

[@excelisfun]

You are welcome, nimrodzik1!!!! Thanks for your support : )

[@usedcarsuae.]

Great informative video 💟

[@excelisfun]

Glad it is informative!! Thanks for your consistent support, Qasr : )

[@ogwalfrancis]

As always, Awesome Videos only, Thank u so much Mike.

[@pmsocho]

So cool!!!

[@excelisfun]

Yes, indeed! Thanks Teammate pmsocho!!!!

[@yayita0587]

Great video! Thanks for making it understandable

[@gentle2005phir]

Super!!!!!!!👍👌 Duper, intense, cross filter, many to many, both, bridge Mike this excelisfun!!!!

[@cindaliang3376]

amazing as always.

[@Durikru]

Magic! Live and learn. Спасибо!

[@excelisfun]

Glad you like it and thanks for your support, Денис Малев!!!

[@malchicken]

Very informative. Thank you.

[@excelisfun]

You are welcome for the bridge to useful information, Hendrick!!!

[@workstuff5253]

Fantastic video Mike. Would you mind expanding this topic to include the impact of RLS across the many to many/bridge table?

[@MalinaC]

I love the solution and "Bil Power Query Poet Szysz" nick ;)

[@excelisfun]

I just made up most of those books, for fun with out Team!!!! Glad you love this, Malina : )

[@fahimanjaria]

You are awesome.

[@excelisfun]

Glad the video helps : )

[@billk1917]

Is there a trade-off between using the Crossfilter approach as opposed to using the Tablefilter approach? The tablefilter looks a little easier to me.

[@excelisfun]

Yes, Table Filter will send all filters backward across Many To One Relationship, CROSSFILTER just activates the one relationship.

[@jarosaw4013]

Thx very much great job

[@excelisfun]

You are welcome!

[@imashiah]

Thanks Mike! Question: If, to the bridge table, you add a PK that also was in fact table as a FK, would you be able to make that connection, thereby making unnecessary the dBooks connection to the fact table? Would this be an improvement to the model?

[@xiaojienan7891]

hi me again, the pdf notes and links etc were unable to be download. if not too much trouble to fix it. thanks very much!

[@calvoenterprises7711]

@excellsfun can you make a simple account receivables using different sheets. thank you

[@lawrence2786]

Thanks for the great videos and tutorials. I love them all. 2 related questions. 1. What would you recommend the best way to pass a parameter to PowerQuery/PowerPivot before refreshing the data (like a date range or Branch in order to reduce the query size). I want to enable users to do this who would not have the knowledge to edit the Query. 2. Can one pass a parameter / edit a step in PowerQuery/PowerPivot via VBA code?

[@williamarthur4801]

Just to re iterate, there's a word we know and love, everything in previous comments.

[@vasilsimonishvili3902]

Hello. Thanks for such videos. I have just 1 question. If for instance, first we unpivot columns (AuthorID-1, AuthorID-2) in table dBooks, how we achieve the same results? for instance, if we have the same 3 tables, with only difference that dBooks has 1 colomn AuthorID, and we have the same goals, how can we do that? Thanks in advance

[@henrytran4782]

Thank you.

[@excelisfun]

You are welcome, Henry!!!

[@Sal_A]

Is it safe to say, bi-directional filter is primarily used for bridge tables when there is a many to many relationship?

[@excelisfun]

No, there are other uses also, like if you need to jump backwards across a many-to-many relationship to make a calculation, but I tend to do that with formulas, like with Table Filters that I have shown in the past...

[@Sal_A]

@@excelisfun Gotcha. When there is a many to many relationship do you always need a bridge table or use cross filter/table filter in order to make a calculation in Power Pivot?

[@excelisfun]

@@Sal_A We always need some method to pass a filter from a Many Side to a One Side, whether bi-directional filter, CROSSFILTER or Table Filter. This is TRUE whether or not we have a Bridge Table. Bridge Tables are for when there is a Many-To-Many Relationship.

[@Sal_A]

@@excelisfun Ah! Thanks for the clarification.

[@michaelalde7661]

Hello Mike, i also want to join in into the grateful community here. I like, u also show possible obstacles first and then how to resolve them. I focusd now in Power tools and Power BI since about one year (sorry Im german lols) since I took a class on EDX Platform. The lecturer from Davidson college uses DAX "Userelationship" to fix the "Many to Many-Relationship". ofc Im aware there are always many roads to Rome .... but I feel still uncertain when to use. Maybe its worth an extra topic in EMT/PBIMT? i watched for a while your amazing helpful videos: Maybe u covered it already , but still lot of fun(work) to do for me...

[@stevennye5075]

well done!

[@mueez89]

Amazing!!

[@salahaddin2009]

Thanks

[@abdulazizalduhayan5026]

thanks mike

[@excelisfun]

You are welcome, Abdulaziz!!!!!

[@oztellingtales5879]

GOOD GOOD GOOD!!!!

[@hosseinhosseinpoor9561]

top

[@simulateduniverse9373]

My head exploded.

[@goutamporel3119]

Sir i am your fan, sir i have a excel problem, excel contain huge data and formula thats why excel size about 50mb, now and then excel open and sometimes excel not open please suggest how to run this excel smoothly for ever

[@excelisfun]

I have no idea. You have not provided any specific detail. Help will require extensive back and forth dialog during consult engagement, for me. For free help and back and forth dialog, try this amazing Excel question site: mrexcel.com. When you post, provide people with clear details so they can try and help.

[@ismailismaili0071]

I'm still not that familiar with Power BI

[@excelisfun]

But when you want shareable and interactive visualizations, it sure is nice : )