Relationship (Strange behavior)

[Anonymous]

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

[amitchandak]

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

[mattww]

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

[Anonymous]

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]

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

[Anonymous]

It didn't work.

[PaulDBrown]

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).

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

 I've attached a sample PBIX file

[PaulDBrown]

What didn't work?