cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rhjaber
Helper I
Helper I

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

 

pbi1.png

 

pbi2.png

 

7 REPLIES 7
amitchandak
Super User
Super User

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

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

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






It didn't work.

All you need to do is create an ID for each blank email (it doesn't even need to be an @ address)

Try this in Power Query:

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtSS1S0lHyys9LLQbSBSB+FojjkJ6bmJmjl5yfCxQ2NDBQitWJVgouLU7MA/LDizLTM0qAjGKQQDmYZ+iQkV+CpMcIosUrPwOkIzg3syQDSGcVgxgOifkwZcYQZb6JJSDzwjMyS1KBNBCZmUL1g/hOOYnJ2RBxC0vs4mZGSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Surname = _t, Email = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Surname", type text}, {"Email", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Name", "Surname"}, {{"Count", each List.Min([Index]), type number}}),
    Source1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtSS1S0lHyys9LLQbSBSB+FojjkJ6bmJmjl5yfCxQ2NDBQitWJVgouLU7MA/LDizLTM0qAjGKQQDmYZ+iQkV+CpMcIosUrPwOkIzg3syQDSGcVgxgOifkwZcYQZb6JJSDzwjMyS1KBNBCZmUL1g/hOOYnJ2RBxC0vs4mZGSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Surname = _t, Email = _t, Value = _t]),
    #"Changed Type1" = Table.TransformColumnTypes(Source1,{{"Name", type text}, {"Surname", type text}, {"Email", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Name", "Surname"}, #"Grouped Rows", {"Name", "Surname"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Count"}, {"Grouped Rows.Count"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Grouped Rows",{{"Grouped Rows.Count", "Index"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Index", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "Temp", each [Name]&[Surname]&[Index]),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "CompEmail", each if Text.Contains([Email], "@") then [Email] else [Temp]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Index", "Temp"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Removed Columns",{{"Value", Int64.Type}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type3",{"Email"})
in
    #"Removed Columns1"

 

 

Which gets you this (before I removed the original email column as the last step).

CompEmail.JPG

Create the dimension table with CompEmail and create the 1:n single relationship:

Relationship.JPG

 I've attached a sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






What didn't work?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!