Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Relationship (Strange behavior)

Hi there,

 

I came across a weird behavior into PBi today. I tried to create a relationship between two tables using the email field, on the table view_leads the email is unique but on the table school_apply it is not. I may have multiple applications coming from the same email. I tried to create a 1toMany relationship but Pbi does not allow me to do that, it only shows ManyToMany relationship.

 

Any ideas? Thanks in Advance

 

 

 

Need Help

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , view_lead does not have unique email or have blank record for email. Check for these 2 first

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Yes, I found a few blank records for email. Is there any way to omit this blank records in order to create that relationship?

      • PaulDBrown's avatar
        PaulDBrown
        Community Champion
        4 years ago

        You can filter out the blanks in Power Query. However, that would potentially leave out rows and leave orphaned records when you use the dimension table. One way of avoiding this is to create an ID column, for example by concatenating name with email. Or creating a fake email for blank values as an ID

    • mattww's avatar
      mattww
      Responsive Resident
      4 years ago

      This happens to me all the time, null emails causing a many-to-many