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 sc...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

It didn't work.

PaulDBrown's avatar
PaulDBrown
Community Champion
4 years ago

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