Many to Many Relationship

[Shamatix]

Hey fellow users of Power Bi,

First of all I wouldnt be here if I hadnt searched and tried figuring this out by my self at first, but sadly without any luck.

My situation will be a bit hard to explain, and english isnt my native language, so bare with me and please dont hold back if you need further explaning or have any questions, I will of course do my best to answer.

I have two "Imports" with advanced options:

Each import uses the two same tables, but with different SQL statements and they go as follows:

"Import" A:

Select *
from D, K
where K.DID = D.dDID  and d_type = 1  and D.Own<> 'You' and D.Dub<> '2' and EntryDate > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0) and (d_date > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0) or d_date is null)

"Import" B:

SELECT *
FROM D, K
Where  K.DID = D.dDID
AND dte_type = 2 AND D.Own<> 'You' and D.Dub<> '2' and EntryDate > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0) and (d_date > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0) or d_date is null)

These two "Imports" doesnt have anything really unique in em and therefor I have made a new column in each "import" called "SKey" based on two other colums as follows:

=[d_ven] & "|" & [Field2] as SKey

This isnt unique either but gets quite close, so now I need to make a many to many relationship.

I have looked up on the internet and created a bridge table by right clicking Skey in each "Import" and pressed "Add as new query", made em both into a table and appended them with eachother, after that I removed all the dublicates:

Now I have tried to build the relationship from the bridged table to one of the two "Imported table" but it STILL pops up with the following error message:

How can this be, I have spent hours on trying to figure out how I can build a many to many relationship between these two imports, but sadly without any luck.

I hope one of you out there could be so kind and try to help me out here, as I am growing grey hair earlier than I should.

Best regards Henrik V.

PS: Once again, sorry for my bad english, but as said it aint my native language, so just let me know if you need any questions answered.

[vanessafvg]

Have you got any blanks in your data?

[Shamatix]

If you want to I would really like to purchase 1-2 hours of help on how to setup my relationships correctly, then you can see my entire structure.. over skype?

[vanessafvg]

There is also one other thing you can do to isolate the problem

in your visual layer on both sides of the relationships 

place 2 tables on the visual desktop canvas

and drag the key field from either side of the tables into their own table - drag  the key field in again and change it to a count, so that it aggregates on the key field.

and then sort by the count field,  in descending order you should see the cullprits on the top.

let me know if you need a visual

[Shamatix]

Sorry, I am quite new to power bi in general and would love to see it visually if possible or perhaps a skype session tomorrow? As said I really do not mind paying in order to get all my relationships working=/

[vanessafvg]

Lol, its late here i am heading for bed,

the only other advice i would give you is

1. In power query under the transform button do format / trim and clean on all fields that you use to make up your key before you make up the key

2. Power query is case sensitive, if you have different cases it will see it as different so de deduplicating wont help because it will see it as unique, however dax sees lower and upper case as the same, so transform your keys into uppper or lower and then only remove duplicates after you have done the above.

3. As i said previously any blanks or null values will also create issues so you will need to pad them with other values ie. replace with -1 or something.

Hope that helps.

[Shamatix]

I tried doing it a bit different, and now I can create some relationships as seen here:

but  if I try drag a column in from each table it says it cant display the visual

[vanessafvg]

And if you click on see details, what does it say?

[Shamatix]

[Shamatix · Accepted solution]

Found a solution, problem was bugs regarding duplicates in power bi

[v-ljerr-msft]

Hi @Shamatix,

Could you post your table structures with some sample data which can reproduce the issue, so that we can help further investigate on it? It's better to share a sample pbix file if possible. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Regards

[Shamatix]

No I do not =/