Can't relate a table with removed duplicates with another table in a "one to many" way

[jfat]

Hi, all. I've been trying to connect two tables that are actually the same.

I have a database with every sale, in which some products appear more than one time. Each product has a specific code (ISBN).

So, I created a new table, a Product Master Table: based on the sales database, I removed every column except ISBN and Title and removed duplicates of ISBN.

But then, when I tried to relate both tables through ISBN columns, the only way it admits such relation is "many to many", even if in the Product Master Table there are no repetitions.

The reason I want to do this is that Titles may be repeated across codes: different books can have the same name. So, I need a Master Product Table to relate to every other table through ISBN, so to get the title.

[v-stephen-msft]

Hi @jfat ,

Based on my test, it's one-to-many realtionship. The Table 2 contains unique ISBN from Table.

No offense, please double-check that the ISBNs in your Product Master Table are unique.

Best Regards,

Stephen Tao

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

[Manoj_Nair · Accepted solution]

@jfat- To address this issue and establish a "one-to-many" relationship between the tables, you can create a unique identifier column in your Product Master Table. This column could serve as a primary key, ensuring that each row in the table has a distinct value. By relating the tables using this unique identifier column instead of the ISBN column, you can establish a proper "one-to-many" relationship.

If this post helps to find solution would be happy if you could mark my post as a solution and give it a thumbs up

Best regards

Manoj Nair

Linkedin - https://www.linkedin.com/in/manoj-nair-%E2%98%81-344666104/

[latimeria]

Hi @jfat ,

I guess you did table.distinct in powerquery.

Powerquery is case sensitive, DAX is not.
Force table.distinct in PowerQuery to ignore case

Table.Distinct(PreviousStep, {"ISBN", Comparer.OrdinalIgnoreCase})

[DmitryKo]

How is this a solution when it ignores data's nature?