Dont Understand the Join

[Schreckofant]

Hi , Im new in PowerBI an i have a problem
I want to join two tables.
The Product ID column is identical in both tables and contains almost 128 Product IDs, each of which is assigned 8 UserIDs and 8 Reviews IDs. I want to join them so that I always have 8 Product IDs, 8 UserIDs and 8 ReviewIDs next to each other.User IDReview ID

But if I now perform an InnerJoin and select both ProductIDs as the matching column, and then expand the column. Then it assigns 8 different reviews to each User ID.

So after expanding, I have 64 times the same Product ID, with 8x 8 User IDs, each with 8 different Review IDs.

After Join

How can I make it so that the columns are simply linked together without it doing this multiple allocation?

[dufoq3 · Accepted solution]

If you want to join them row by row (you have same 128 rows in each table and 8 rows per ID) you can add index to both tables and create such join with INDEX column.

[dufoq3]

Hello again @Schreckofant, you can even use this code. Create blank query, open Advanced editor and replace whole code with this one:

let
    Source = Table.FromColumns(Table.ToColumns(UserID) & Table.ToColumns(ReviewID[[ReviewID]]), Value.Type(Table.FirstN(UserID,0) & Table.FirstN(ReviewID[[ReviewID]],0)))
in
    Source

[Schreckofant]

Ok, thank you :).
Is there a way to add this column exactly as it is, which would be easy in Excel, but seems impossible here.

[uzuntasgokberk]

Hello @Schreckofant ,

You can make relationships with data modelling instead of join in Power Query. However, you made data modelling and put the columns that are product_ıd, user ıd and reference ıd, you will get same results. You need more column that needs to be joined. 

Best Regards,
Gökberk Uzuntaş

LinkedIn: https://www.linkedin.com/in/g%C3%B6kberk-uzunta%C5%9F-b43906198/

Medium: https://medium.com/@uzuntasgokberk

İf this post helps, then please consider Accept it as solution and kudos to help the other members find it more quickly.

[uzuntasgokberk]

Hello @Schreckofant ,

You joined only one spesific column which is product ıd. Therefore, in User ID for example for product ıd "...4JI" that have eight different User ID. For Revıew table again same "...4IJ" that have eight different reference ID. When it matches only Product ID they both have eight different value for user ıd and reference ıd so that will be 64 row for only "...4JI" But, assume that you have also user ıd in your review table. and joined both product id and user id, you will get different result. 
You can reference this link:
https://www.geeksforgeeks.org/sql-join-set-1-inner-left-right-and-full-joins/

Best Regards,
Gökberk Uzuntaş

LinkedIn: https://www.linkedin.com/in/g%C3%B6kberk-uzunta%C5%9F-b43906198/

Medium: https://medium.com/@uzuntasgokberk

İf this post helps, then please consider Accept it as solution and kudos to help the other members find it more quickly.