Forum Discussion

Shamatix's avatar
Shamatix
Post Partisan
9 years ago
Solved

Many to Many Relationship

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.

 

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

11 Replies

    • Shamatix's avatar
      Shamatix
      Post Partisan

      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's avatar
        vanessafvg
        Community Champion

        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.