Building relationships between tables when both have duplicates

PowerPivot and Power BI Desktop only allow the creation of relationships between tables when one of columns forming the relationship does not have duplicate values.
In order to relate these two tables to one another, what we need is a third table with all distinct values from both tables. In this tutorial we will show you how to do just that.
Link to Power BI file used in the video, get file #41 on the community downloads folder: curbal.com/donwload-center
SUBSCRIBE to learn more about Power and Excel BI!
/ @curbalen
Our PLAYLISTS:
- - Join our DAX Fridays! Series: goo.gl/FtUWUX
- - Power BI dashboards for beginners: goo.gl/9YzyDP
- - Power BI Tips & Tricks: goo.gl/H6kUbP
- - Power Bi and Google Analytics: goo.gl/ZNsY8l
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/contact
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
► Twitter: @curbalen, @ruthpozuelo
► Google +: goo.gl/rvIBDP
► Facebook: goo.gl/bME2sB
#SUBSCRIBE #CURBAL
► Linkedin: goo.gl/3VW6Ky

[@jda817]

THIS WAS THE FIX!!! Thank you for providing something of value and not ruining it with fluff. My project can proceed!

[@CurbalEN]

Excellent! Go,go,go! /Ruth

[@Everyonelovesyou]

Hi, I have learnt a lot from your videos...I am PQ user since 2016....This is one of those rare solutions which I explored with my common sense in beginning......and always believed that this is just a way round.....Happy to see it is authenticated in your tutorials......Stay Blessed

[@leonidiakovlev]

It is a good habit to create lookup tables for every dimension at the model design stage. These 2 tables in the beginning of the video are actually two fact tables with shared dimension (Product), bu different metrics (Sales and Stocks). What Curbal did was the creating the Product lookup table. Elegantly presented and explained, Curbal! Thanks!

[@CurbalEN]

Thanks Leo, /Ruth

[@giorgioiribarren]

Rut, es increíble!. Segunda vez en el mes que viendo tus videos e solucionado un problema en mis reportes que en ninguna otra parte me han orientado de la forma precisa que lo haces. Muchas gracias. Estoy seguro que el curso de Curbal es potente. Voy a ahorrar unos pesos para pagarlo. Definitivamente. Y adicionalmente, disfruto con tu carisma y encanto!. Gracias Rut, espero entiendas español. Saludos desde Santiago de Chile.

[@CurbalEN]

Hola Jorge! Si,si, soy española (Asturiana) :) Me alegro mucho de que mis vídeos te sean útiles!! Saludos ! /Ruth

[@YHWH979]

Thank you Miss. Ruth for your kind video, you helped me at the moment when I am about to give-up my project

[@hendrik-stack22]

6 years later and still relevant, THX Curbal

[@aliciapeel9561]

This is explained so simply thank you! One thing I've been struggling with is how you go about this if you want to create a new 'ID' based on multiple column values. If I do it this way as that ID table links to the original table so I then have to create a new data table with the ID table and original table merged. I have no idea if this is a good way to go about it but its the only way I've been able to get my head around the problem!

[@sumantjoshi490]

Thanks Ruth for explaining complex scenario in a simple way. Really understood the concept in detail. Many thanks ❤️❤️

[@CurbalEN]

🎉🎉

[@codingwannabe]

Love your channel, the more I browse your videos the more I'm tempted to subscribe to your Power BI courses. Great job!!

[@CurbalEN]

Thanks! /Ruth

[@nicholasspaeth9790]

This exact solution didn't work for my particular case, but it gave me a better understanding of what I needed to do. Thanks to this, I was able to resolve my troubles!

[@CurbalEN]

Lovely to hear!

[@immanuelh4637]

You have virtually taught me everything I know about power bi for free. Thank you very many much. I wish you a happier new year.

[@CurbalEN]

😃

[@nancmadi]

THANK YOU SO MUCH FOR MAKING THIS SO SIMPLE... I have been trying to find this fix everywhere!

[@CurbalEN]

Glad to hear that! :) /Ruth

[@AlvinRyellPrada]

This is awesome! I really love the fact that you made it simple and easy for me to understand it. Short video yet powerful. Thank you for advocating the making the complex simple in powerbi! 💜💜❤️

[@CurbalEN]

And thanks for the feedback!

[@AllscanTwelve]

Thanks for the tutorial. It really works! My question now is, why "(Blank)" appears in one of the selections using Slicer? How can I get rid of it?

[@hatim4436]

Thank you for the video! It help me solve almost all my problems. Some things that I found out: 1. You can use same concept in excel 365. 2. If your data is date based, then create a table of unique date and connect it in between the 2 original tables that you had. Again, thank you @Curbal!!!

[@CurbalEN]

Thanks for sharing!

[@nicholasspaeth9790]

I wish I read your comment yesterday! Good stuff!

[@JenntheGeek]

Thank you for posting this, it worked nicely and fairly simple for a power BI beginner like me :)

[@piroro195]

helped after trying to figure it out for the past two days whew.. Thank you so much

[@cyrub2132]

You are amazing!!!! Every time I have a problem you always have a nice solution. Thank you so much! :)

[@CurbalEN]

Excellent ! Happy to be useful :) /Ruth

[@fernandoferreira9062]

I'm so happy I found this video! Thank you a lot!

[@CurbalEN]

You welcome :))) /Ruth

[@navitaw8876]

Exactly what I was looking for. Thank you very much!

[@CurbalEN]

Yey!! You welcome!! /Ruth

[@alsch4387]

Thank you so much for your solution , I find the many ways to resolve this problem.

[@qualityrevolution]

Thanks for this , I tried the same on my data , relationships got created with common column/list (enquiry no. In my case) but I am still not able to fetch data which is in table 1 (source of enquiry in this case) but not in table 2 . Kindly suggest what might be the problem. It still says relationship needs to be established .

[@mariomdssaldanha5353]

Thanks so much for your guidance and easy explanation of concepts.

[@robertshore2989]

Amazing - you've made that so simple to understand after days searching for a solution.

[@CurbalEN]

Yey!!Music to my ears!!

[@robertshore2989]

@@CurbalEN I'll never understand why Power BI complicates tasks that are so easy in Excel - again - thank you and you have a new subscriber here.

[@HarishS12137]

Thank you for this tips, much needed for a beginner!!

[@CurbalEN]

Awesome!! /Ruth

[@eriol33]

thank you for this. I think I have been doing things wrong with power BI as I did left join to my tables instead of creating a relationship, I suffer a very big performance penalty despite relatively low number of tables. I think this is the best practice!

[@CurbalEN]

Yes!! That is the way most of us travelled before we found a better path. Just keep going! /Ruth

[@amandafernandesramospasson5862]

THANK YOU! this was just what i was looking for

[@CurbalEN]

🥳 /Ruth

[@lakshmikalyanir4510]

Thanks for sharing the trick. And here I have one question in the same scenario instead of joining on one column(ProductID Key), if I want to join on two columns how its gonna work. Can you please suggest on that.

[@agapheputrasusilo741]

Life saver, i was looking for this. thank you

[@AndyTranLove]

Thank you. I have done the same but I got the result value was "1" for each unique item. Do you know why?

[@raghukumar4483]

Thanks for this. Could you let me know if I want to get 'StockLocation' column from 'StockTable' table to ManufactTable

[@Sneha2490]

Hi Ruth, this is a great video. However is there any prerequisite for the columns to have same format. For example - if one is formatted as a whole number and one is text+number, can I still create a relationship through a unique column? Please help.

[@alexandrgavrilyuk4090]

simple and easy way at least for beginners! many thanks!

[@CurbalEN]

Thanks Alexandr! :) /Ruth

[@swarnalathareddy]

Does that new query gets updated if any new product ID added in to original tables

[@stududs]

Thank you , at last a clear simple explanation I can understand!

[@CurbalEN]

🥳🥳

[@phorton968]

Beautifully elegant! You are the Power Bi fairy godmother.

[@CurbalEN]

Sprinkling knowledge everywhere ✨

[@andreawilliams9793]

Helped me a lot thanks, i was struggling with exactly this problem, thanks again

[@CurbalEN]

Wonderful Andrea, glad it was helpful! /Ruth

[@asifshaheer]

Very nice and simple approach...thanks for helping us solving such problems

[@CurbalEN]

+Asif Hi Asif! My pleasure to be able to help! Thanks for your feedback! :) /Ruth

[@RajanieshKaushikk]

You ate the master of powerBI and DAX

[@CurbalEN]

Thanks, glad you like my videos! /Ruth

[@giri41]

Good one .. please do a video on filter dates in table affect the table

[@LotfyKozman]

Nice trick and helps in solving many problems. Thanks

[@CurbalEN]

+Lotfy Kozman You welcome Lofty, glad it helped you :) /Ruth

[@yishaktaye1951]

Very Helpful video. Thank You.

[@sembitoumbou8989]

You're a LEGEND. Thank you ma'am

[@bigpapa112998]

Thank you, this was great exactly what I needed.

[@CurbalEN]

Excellent! Glad it helped :) /Ruth

[@gayatripordwal6721]

Hello Madame I want to know how do u do this if your both tables have other columns as well which are duplicates

[@abhijitdeshpande92]

Hi, I have multiple columns having multiple duplicate. with above mentioned method I am able to manage relationship between columns with same name. Now i have 3 such common column in each table. If I perform any filtering or slicing it applies to only one column. How to proceed when I need to mange relation between these 3 newly created table.

[@kelliecook4000]

Thank you-Very helpful. I did have a null value in my data I had to delete after I did all that and then I was able to make my relationships.

[@CurbalEN]

+Kellie Cook Glad it worked :) And thanks for the feedback! /Ruth

[@NorbertoVeraReatigaNVR]

Muchas gracias, Ruth!!, Excelente truco...

[@CurbalEN]

+Norberto Vera Reatiga Verdad? Sencillo y rápido de hacer! /Ruth

[@kiasca3489]

I new to power Query, what about if one table has duplicate but you need the sum of total amount count versus another table which is total sales.

[@user-wg1ns3yu3j]

Thanks, you saved me half a day!

[@CurbalEN]

At your service :)

[@Laxmanmane007]

Thanks for valuable video I have one Query I have 3 tables and each table have 2 common column I e dept & sub dept Now I have created separate table for dept and also separate table sub dept table and given realtionship to 3 tables & also to dept but my sub dept slicer is not working could you please help

[@immanuelh4637]

I love you videos. Thank you so much

[@wisnudanang5819]

Thank you for sharing !! This was the fix, i wish you great great all time

[@CurbalEN]

Me too :)

[@shafiulamin4128]

Fantastic. Very helpful trick.

[@lisbongraffiti242]

When you add this kind of tables for a many-many relationships it can be considered a star model or is a different kind?Thank you

[@jerzsubbie]

Thank you, this helps me a lot!

[@dennismitchell4836]

This was so useful, thank you.

[@CurbalEN]

You welcome!! /Ruth

[@jonascarvalho7331]

That was awesome! That trick will spare tons of wasted times for me!

[@CurbalEN]

Yey!!! Mission accomplished ;) /Ruth

[@KrazyWorldWars]

I been looking for this for past several days ...

[@CurbalEN]

Here you have all my content indexed: curbal.com/curbal-learning-portal

[@ignacio8428]

te amo con todo mi corazón, he estado intentando hacerlo con DAX pero me complique la vida, muchas gracias, te amo

[@CurbalEN]

+ignacio jerez Gracias Ignacio! Menudas pasiones despierta Power BI!!! 😄 Muchos saludos desde Suecia, Ruth

[@ignacio8428]

I have one question, ¿ como puedo relacionar 3 o 4 tablas? , ¿ Se puede por este método? , a mi me dio error cuando intente por este método crear otra combinación entre tablas, ¿Como lo hago?

[@CurbalEN]

+ignacio jerez Hola Ignacio, depende. Tienes que tener una columna común en cada tabla para poder unirlas. /Ruth

[@ignacio8428]

Una consulta cuando creo otra tabla new Query no me deja hacer una combinación many to many with make this relationship active, tengo un error ( I cant create a direct active relationship between Tabla1 and Tabla2...) , ¿Que puedo hacer?, ¿What can I do?

[@CurbalEN]

+ignacio jerez Hola Ignacio, no Es posible hacer combinaciones many-to-many en Power BI! /Ruth

[@saikathbose8120]

Very useful! Thank you!

[@pankajkumar-oj8mr]

If I have two fact tables and one fact table is already mapped with dimension tables. Other fact table shows deactivate relationship with first one to make this active with Dimension table. What is the fix for that? I need both the table's relationship to be active to show correct data.

[@jereyeses]

Muchas Gracias! Maravilloso!

[@CurbalEN]

+Jesús Reyes De nada Jesús! /Ruth

[@fuberlin1]

2020 this is still great help! helped me finish work :))

[@CurbalEN]

Yey!!

[@schipuu6669]

Where can i write in the Power BI Community? Do you have a Link? Thänx :-)

[@kdub000000]

Thanks! How to do this on a column with two formulas (column A & column B) to make unique ID, however those calculated columns do not show up in Query editor?

[@CurbalEN]

Try joins with DAX: curbal.com/blog/joining-table-in-power-bi-with-power-query-and-dax For more detailed answer, post your question in the power bi community. /Ruth

[@blondeunicrone2695]

My gosh this is sooo helpful ❤

[@MaiTran-xf3op]

Thank you so much for this video since I have technique to deal with my file with the same problem that is posed in your video. thanks a lotttttttttttt!

[@CurbalEN]

🥳🥳

[@MaiTran-xf3op]

@@CurbalEN I don't know why I do the same thing but it doesn't take effect. the key point is that the unique value is the flight in the single day, I create the list like you with column flight no and flight date. But then after I create relationship between 3 table, 2 data table with the list already created, pivot table still asks me to create relationship and result is not true. Could you kindly explain this for me?

[@dashingmozy111]

Thank you so much!!

[@Laxmanmane007]

Thank you so much👍👍

[@ankitatripathi9743]

Nice Mam and for practice how will i get data ?

[@just3963]

Very useful!! Thanks for sharing! :)

[@CurbalEN]

You welcome! /Ruth

[@leonardo03231315]

Thanks but unfortunately still stuck with excel this doesn’t seem to solve the issue in that the relationship is not recognized in slicer but there in power pivot

[@ba1dwin]

Thank you very much. Very helpful indeed.

[@CurbalEN]

Excellent! Glad it helped :) /Ruth

[@SHperth]

Hi Ruth, thanks for keeping sharing power bi good stuffs. Questuon: why is the total 237 which seems higher than the sum of above 2 values.

[@CurbalEN]

Check this video: m.kzbin.info/www/bejne/q5ergIKCmc6SoM0 /Ruth

[@jiyaadnaeem4802]

Thank you so much, it's like magic!

[@CurbalEN]

Pure loooooove ;) /Ruth

[@xiomaragotch9453]

very helpful. thanks

[@TroyLambert]

Would like to see examples with multiple columns and selective data

[@orangutanklaus7035]

AWESOME!!!!! Thank you so much!!!

[@CurbalEN]

👏👏👏 /Ruth

[@justpassingby6106]

Hi, It didn´t work between power query to power pivot :( help please!

[@josebernaldez]

Wow, que sencillo truco y yo rompiendome la cabeza.... muchas gracias.

[@CurbalEN]

+José Bernáldez Muchas gracias a ti por el comentario, es un placer poder ayudar :) Saludos, /Ruth

[@jagdishbr5297]

Hi Ruth, Thanks for posting this video Is it possible to share the complete data or link where i can download to get hand on.

[@CurbalEN]

If there is no link in the description box, I am afraid I don’t have it anymore! /Ruth

[@mohammadazadi3616]

very goog toturial. thanks a lot

[@cardguys]

This is helpful. I wonder how to do this with DAX summarized tables.

[@CurbalEN]

Check the DAX joins, it should help: curbal.com/blog/joining-table-in-power-bi-with-power-query-and-dax /Ruth

[@guptamols]

Thanks, this solved the problem however now if we had to build Product Hierarchy e.g Product Group > Product Types > Product. How do we do that?

[@CurbalEN]

Can you post the question in the power bi community? Make sure you post some sample data too! /Ruth

[@coolboyboy2087]

solved my problem!! thanks much!!!

[@CurbalEN]

Yes!!! Music to my ears 🎶 /Ruth

[@84257542]

Thank you so much

[@rohitnegi6376]

Thanks Ruth for the video...it’s helpful.... if you don’t mind I have a question.... I have three tables with three similar columns or headers along with some data... when I create a unique I’d by merging the three columns to make a unique I’d (as separate column) it creates the relationship...however when I create the graph/table it doesn’t give the right data..we need to show data with these three headers filters...please help in resolving the same or please suggest if anything else can be done to create a relationship...

[@CurbalEN]

Hi Rohit! Can you post your question in the power bi community? Please make sure you give sample data ! /Ruth

[@mattmatt245]

Do you need bi-directional filter enabled to make this work ?

[@CurbalEN]

No, you don’t. /Ruth

[@ruroshinzynaruto]

Perfect. Thank You

[@CurbalEN]

My pleasure ;) /Ruth

[@sunandamadhuri9355]

Hi ,I am not able to get the data when i am trying to put the columns from both the tables.it is showing an error that the relationships are not found.please help.

[@MaximilianoDeLaTorre]

Hi thanks for the video!!! I tried this on powerPivot and wasn't abel to complish it, do you know if this only works on powerBi?

[@nicholasspaeth9790]

It helped me resolve my scenario in PowerPivot.

[@javedkhan-tz6fn]

Great tip

[@suryapavann2572]

Thanks a ton Ruth Pozuelo.:-)

[@CurbalEN]

+suryapavan n You are most welcome:) /Ruth

[@SunnyGoklani9]

Thank you - this came pretty close to solving the problem I had! But instead of giving error due to duplicate values.. When I follow this and create a link-key table, I can link key table to Table 1, but cannot connect it to Table2 (it says it will create ambiguity as indirect relationship already exists between the 2 tables). I want the other primary link to stay. What do I do?

[@CurbalEN]

Difficult to tell without knowing the details. Can you post your case in the power bi community? Make sure you explain your model. /Ruth

[@mahanteshc9374]

good. But unable to download source files. Please assist me to get it.

[@faisalzahoor7800]

How we create a unique list of primary key , if we don't have primary key in both the tables ..... I think using merge & append to make a consolidated table is better than creating a relationship.

[@vagnao1982]

Thank you! You have help-me!

[@CurbalEN]

Excellent, happy to help! /Ruth

[@bharatarse1503]

Amazing i really like it👍 ..keep sharing..☺️

[@CurbalEN]

😊

[@AGBDej]

Thank you!