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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
marcomarzano
Frequent Visitor

Many to many relationship not wanted

I have a problem in creating a relation between two tables because it creates a many to many relationship while I'd like it to be a one to many in order to use related().

I have an excel table listing all product descriptions (let's say table A) and i checked in excel that there are no duplicates in the field DESCRIPTION that i want to use as a key.

I then have another excel table with data from production (let's say table B) where, obviously, every product descriprion is repeated several times.

When I try to estabilish a relationshib between these two tables (A and B) PBI says that the relationship is a many to many and not one to many.

I really don't know where the problem is but i think that there is a problem with products like these

 

87.23  Radiografia della colonna toracica (dorsale) - (2 proiezioni)
87.23 Radiografia della colonna toracica (dorsale) - (2 proiezioni)


As you can see they look the same but in excel they are not the same because in the first row it is 87.22spacespaceRadiografia while the second row is 87.22spaceRadiografia.

I have several products like these in my A table.

 

I've even tried to remove duplicates in PBI but nothing happens.

I don't know if the cause of the many to many relationship is this but I really don't know how to solve this.

Thank you so much

6 REPLIES 6
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @marcomarzano ,

 

By my tests, the "a  a" and "a a" in not the same in Power BI.("a  a": a space space a; "a a": a space a)

 

If it is convenient, could you share some data sample which could reproduce your scenario so that I could have a test on it.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-piga-msft 

thank you very much.

I don't know how to attach an excel file here.

Hi @marcomarzano ,

 

You could upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Ok, i'm trying to do as you suggested but seems like my organization blocks the use of one drive...

I'm thinking about a workaround.

Is it possibile in Query editor to remove all blank spaces in a text?

I've tried transform->trim but it doesn't seem to remove all the blank spaces.

This way i think i could solve the problem.

HotChilli
Super User
Super User

You have found the problem.

You either need to clean up the data OR use a different table setup.  It is quite unusual to link tables by using a long text field as a key.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.