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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

It didn't work.

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

    What 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