Building relationships between tables when both have duplicates
HTML-код
- Опубликовано: 18 окт 2016
- 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
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!
Thanks Leo,
/Ruth
THIS WAS THE FIX!!! Thank you for providing something of value and not ruining it with fluff. My project can proceed!
Excellent! Go,go,go!
/Ruth
6 years later and still relevant, THX Curbal
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
Thank you Miss. Ruth for your kind video, you helped me at the moment when I am about to give-up my project
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.
Hola Jorge!
Si,si, soy española (Asturiana) :)
Me alegro mucho de que mis vídeos te sean útiles!!
Saludos !
/Ruth
Love your channel, the more I browse your videos the more I'm tempted to subscribe to your Power BI courses. Great job!!
Thanks!
/Ruth
Thanks Ruth for explaining complex scenario in a simple way. Really understood the concept in detail. Many thanks ❤️❤️
🎉🎉
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! 💜💜❤️
And thanks for the feedback!
THANK YOU SO MUCH FOR MAKING THIS SO SIMPLE... I have been trying to find this fix everywhere!
Glad to hear that! :)
/Ruth
Exactly what I was looking for. Thank you very much!
Yey!! You welcome!!
/Ruth
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.
😃
I'm so happy I found this video! Thank you a lot!
You welcome :)))
/Ruth
THANK YOU! this was just what i was looking for
🥳
/Ruth
You are amazing!!!! Every time I have a problem you always have a nice solution. Thank you so much! :)
Excellent ! Happy to be useful :)
/Ruth
Thank you for posting this, it worked nicely and fairly simple for a power BI beginner like me :)
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!
Thank you for this tips, much needed for a beginner!!
Awesome!!
/Ruth
Thanks so much for your guidance and easy explanation of concepts.
Beautifully elegant! You are the Power Bi fairy godmother.
Sprinkling knowledge everywhere ✨
Muchas gracias, Ruth!!, Excelente truco...
+Norberto Vera Reatiga Verdad? Sencillo y rápido de hacer!
/Ruth
Amazing - you've made that so simple to understand after days searching for a solution.
Yey!!Music to my ears!!
@@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.
Nice trick and helps in solving many problems. Thanks
+Lotfy Kozman You welcome Lofty, glad it helped you :)
/Ruth
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!
Lovely to hear!
Helped me a lot thanks, i was struggling with exactly this problem, thanks again
Wonderful Andrea, glad it was helpful!
/Ruth
Thank you, this was great exactly what I needed.
Excellent! Glad it helped :)
/Ruth
simple and easy way at least for beginners! many thanks!
Thanks Alexandr! :)
/Ruth
Very Helpful video. Thank You.
Thank you , at last a clear simple explanation I can understand!
🥳🥳
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!!!
Thanks for sharing!
I wish I read your comment yesterday! Good stuff!
helped after trying to figure it out for the past two days whew.. Thank you so much
Life saver, i was looking for this. thank you
Muchas Gracias! Maravilloso!
+Jesús Reyes De nada Jesús!
/Ruth
I been looking for this for past several days ...
Here you have all my content indexed:
curbal.com/curbal-learning-portal
Very nice and simple approach...thanks for helping us solving such problems
+Asif Hi Asif! My pleasure to be able to help!
Thanks for your feedback! :)
/Ruth
You're a LEGEND. Thank you ma'am
This was so useful, thank you.
You welcome!!
/Ruth
Thank you, this helps me a lot!
Very useful! Thank you!
You ate the master of powerBI and DAX
Thanks, glad you like my videos!
/Ruth
Thanks, you saved me half a day!
At your service :)
Thank you so much for your solution , I find the many ways to resolve this problem.
Fantastic. Very helpful trick.
I love you videos. Thank you so much
Perfect. Thank You
My pleasure ;)
/Ruth
Thank you so much, it's like magic!
Pure loooooove ;)
/Ruth
AWESOME!!!!! Thank you so much!!!
👏👏👏
/Ruth
Thanks a ton Ruth Pozuelo.:-)
+suryapavan n You are most welcome:)
/Ruth
Very useful!! Thanks for sharing! :)
You welcome!
/Ruth
Thank you very much. Very helpful indeed.
Excellent! Glad it helped :)
/Ruth
2020 this is still great help! helped me finish work :))
Yey!!
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?
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!
Yes!! That is the way most of us travelled before we found a better path. Just keep going!
/Ruth
solved my problem!! thanks much!!!
Yes!!! Music to my ears 🎶
/Ruth
Thank you for sharing !! This was the fix, i wish you great great all time
Me too :)
Thank you! You have help-me!
Excellent, happy to help!
/Ruth
Wow, que sencillo truco y yo rompiendome la cabeza.... muchas gracias.
+José Bernáldez Muchas gracias a ti por el comentario, es un placer poder ayudar :)
Saludos,
/Ruth
Thank you so much!!
Thank you so much👍👍
My gosh this is sooo helpful ❤
Thank you so much
very helpful. thanks
thank you it was very helpful
Wonderful! And thanks for the feedback :)
/Ruth
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 .
Great the way :) and the teacher is great!!! :)
😂
That was awesome! That trick will spare tons of wasted times for me!
Yey!!! Mission accomplished ;)
/Ruth
2:40 - 4:20 can be done with 1 step : )
Edit, New Formula:
= Table.Distinct( ManufactTable[[ProductID]] & StockTable[[ProductID]] )
The "Table[[Column]]" selects 1 column from a table.
The " & " combines the two 1-column tables together, one on top of the other.
The "Table.Distinct" removes duplicate IDs from the combined table.
Use this formula in a separate query, and then load to data model to get your join table.
-----------------------------------------------------------------------------------------------------------------
Old Formula:
= Table.FromColumns( { List.Distinct( ManufactTable[ProductID] & StockTable[ProductID] ) } )
It's a good thing to know its possible that way, but for beginners, which most of us here are, its way too complicated.
Where again should you write this query?
@@AkshayKumar-vd5wn It should be a separate query from the "ManufactTable" and "StockTable" queries. At to top, you can click the "New Source" dropdown (next to close & apply), then click "Blank Query". Copy the formula above into the formula bar, then load to data model.
@@dekmar7954 Thanks a bunch. I'll follow up with you when I test this out.
why dont you have videos. I would love to follow you
Thank you!
Thanks Ruth 🙏🙏
Excellent Mam!
+Asim Abdul Wahab Thank you Sir!
/Ruth
great short explanation
+Stephanie Giovannitti Great, thanks!
/Ruth
Thank you
very goog toturial. thanks a lot
Excellent! Thanks for Sharing!
My pleasure :)
/Ruth
@@CurbalEN can this only be done in Power BI, i cant seem to find the drop down in query viewer to add new query in office 365..
Not sure what you mean with office 365, is it excel?
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.
+Kellie Cook Glad it worked :) And thanks for the feedback!
/Ruth
te amo con todo mi corazón, he estado intentando hacerlo con DAX pero me complique la vida, muchas gracias, te amo
+ignacio jerez Gracias Ignacio! Menudas pasiones despierta Power BI!!! 😄
Muchos saludos desde Suecia,
Ruth
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?
+ignacio jerez Hola Ignacio, depende. Tienes que tener una columna común en cada tabla para poder unirlas.
/Ruth
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?
+ignacio jerez Hola Ignacio, no Es posible hacer combinaciones many-to-many en Power BI!
/Ruth
Good one .. please do a video on filter dates in table affect the table
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 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?
Thanks for this. Could you let me know if I want to get 'StockLocation' column from 'StockTable' table to ManufactTable
Great tip
Clever you! Thanks.
Amazing i really like it👍 ..keep sharing..☺️
😊
Allah razı olsun :)
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.
Thanks a lot!
+Faisal Mughal My pleasure :)
/Ruth
Million THANKS
Great solution
Thanks !
/Ruth
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.
Thank you :)
thank you ,its awesome
Fab 😊
wow. amazing content.
Thanks!! :D
/Ruth
Happy to learn 💕
👏👏
Thanks Ruth :)
You welcome !
/Ruth
WOWW , Thanks so much
Thanks 🙏👍
Amazing . Awesome.
Brilliant!!!!!!!!
Thank youuuu :)))
/Ruth
Thanks for trik 🙏😊
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...
Hi Rohit! Can you post your question in the power bi community?
Please make sure you give sample data !
/Ruth
Does that new query gets updated if any new product ID added in to original tables