Skip to main content
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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.


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.

Advocate IV
Advocate IV

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.

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

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors