Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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
Hi @marcomarzano ,
You could upload it to OneDrive and post the link here. Do mask sensitive data before uploading.
Best Regards,
Cherry
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.
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.
User | Count |
---|---|
125 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |