One-to-Many Relationship with Unique Values Not Working

Hello npate102 and to all struggling with this issue.

When importing data from excel files in order to create a dimension ang connecting it later to our fact table it is needed to transform the text data that will contain the unique values BEFORE applying remove duplicates to minuscule.

The reason why is that If not Power Query will detect that for example "mvp_product" and "mvp_PRODUCT" are different ones because PowerQuery is case sensitive. The problem once applied remove duplicates and blanks is that if happens the former example, once imported to the model Power BI changes the name to its standard to capital letter row. So you will have two rows being named "mvp_PRODUCT" and trying to connect that column from your dimension table to the fact table Power BI will detect it as a relationship of many to many. As you will not have unique values any more in that column.

So to sum up the correct steps for transforming excel data which contains text values in the common column to which create the relationship are:

1.- Remove columns you don't want / select the ones you want to keep.

2.- Change data type.

3.- Apply minus/capital letters to the column you desire to connect to your fact table.

4.- Remove duplicates.

5.- Remove blanks.

I hope it helps.