cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
npate102
Frequent Visitor

One-to-Many Relationship with Unique Values Not Working

So, I've two tables (Excel Files) named 'Item Master' and 'Milestones'.

 

Item Master has roughly 15 columns with a column named "Part Number". Milestone also has a column named "Part Number".

 

Item Master has roughly 130,000 rows, while Milestones has 50 rows. 

 

Item Master should have unique values in it, but, there're not. So, I connected to the file with Power BI. Went to query editor screen, and removed,

1. Remove Duplicated from Part Number column

2. Remove Empty from Part Number Column

3. Remove Blanks from Part Number Column

4. Remove Errors from Part Number Column

 

So, technically, that should give me Unique Part Numbers in Item Master table.

 

Now, 'Milestones' table has (and need to have) duplicate Part Numbers in it. But, that shouldn't be a problem as long as there're unique values in one table to create 'One-to-Many' relationship between these two tables.

 

I am unable to do so! Only option is permitted is 'Many-to-Many' saying you can't create 'One-to-Many' between these two tables. To be on the safe side, I also removed blanks, errors on Milestones Tables, and it still doesn't work. This is the first time I am facing this issue.

 

Any idea, what might be happening? This seems so simple thing to do and I've done this dozens of time before.

 

5 REPLIES 5
JasserBI
Frequent Visitor

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.

Data-Rainer
Advocate I
Advocate I

Greetings from New Zealand,
I just had the same issue and couldn't find any duplicates, nor null values in my Excel file.
However after importing my Excel file into Power BI I noticed that Power BI had imported two more rows at the end than I expected from my Excel file and thus created Null value rows.

It turns out that Power BI can import additional Null value rows from Excel.
The safe solution to prevent this is
   to change the data in Excel into an EXCEL-TABLE first (mark the range > Insert > Table)
   then import it to Power BI

This way no more null rows are created and the relationship 1:Many is possible.

HotChilli
Super User
Super User

Normally for these type of issues, i recommend using the Column distribution, quality and profile features from the View menu in Power Query but if there are 100,00+ values it will be of limited use!

Column quality might help though.  And Column Distribution will give you a summary of how many unique values you have (should be the same as the row count)

You can also solve these issues with DAX.  Create a COUNT measure on part number.

Drag it to a table, drag part number as well.  Then order the table by the measure (highest to lowest). 

All values are supposed to be 1 so look for non-1 values

Tried that. Column Quality & Distribution shows 1000 distinct, 1000 unique, 100% valid, 0% error, 0% Empty. Ofcourse, I've 100,000+ part numbers so it's not that useful.

OK try the DAX, it should be quite quick to do.

 

Additionally, when using profiling and quality, the default is to sample 1000 rows.  The whole dataset can be sampled by clicking on 'column profiling based on top 1000 rows' at the bottom of the power query screen.  The other option is shown '...based on entire dataset' .  Warning : it may take a long time

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.

Top Solution Authors
Top Kudoed Authors