Forum Discussion

jdubs's avatar
jdubs
Icon for Helper V rankHelper V
4 years ago
Solved

Merged table - Match on two potential columns?

I have created a merged table where I am matching up phone call records with Contacts in Dynamics. I am matching the phone number from the phone call to the primary phone number on the Contact record. How can I also match on a secondary phone number (e.g. mobile) in the event there is no match on the primary?

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, jdubs ;

    You could merge it in power query.

    1.merge it

    2.expand name.

    3.merge it again.

    4.expand name.

    5.merge two columns.

     

    The final output is shown below:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8sxLyi/NS1HSUTIyNNI1NDLWNTE1M1eK1YlW8i8tQZaztDDXNTM1MQbLoWkzNARhQ6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Call Type" = _t, #"Phone Number" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Call Type", type text}, {"Phone Number", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Phone Number"}, #"Table (2)", {"Home Phone"}, "Table (2)", JoinKind.LeftOuter),
    #"Expanded Table (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table (2)", {"Name"}, {"Table (2).Name"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded Table (2)", {"Phone Number"}, #"Table (2)", {"Mobile Phone"}, "Table (2)", JoinKind.LeftOuter),
    #"Expanded Table (2)1" = Table.ExpandTableColumn(#"Merged Queries1", "Table (2)", {"Name"}, {"Table (2).Name.1"}),
    #"Merged Columns" = Table.CombineColumns(#"Expanded Table (2)1",{"Table (2).Name", "Table (2).Name.1"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged")
in
    #"Merged Columns"


    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

10 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    jdubs , Merge once more using a secondary phone number.

     

    Or try a two join approach  -https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

  • jdubs's avatar
    jdubs
    Icon for Helper V rankHelper V
    4 years ago

    Thanks amitchandak. Can you clarify how I would do this?

     

    Example source data:

    Phone Call Table

    Call TypePhone Number
    Inbound212-123-4567
    Outbound212-987-6543
    Inbound212-111-1111

     

    Contact Table

    NameHome PhoneMobile Phone
    Bob212-123-4567 
    Mike 212-987-6543
    Jim212-555-5555212-111-1111

     

    Desired Merged Table (Matching on either Home OR Mobile Phone Number):

    Matched Calls

    Call TypeNamePhone Number
    InboundBob212-123-4567
    OutboundMIke212-987-6543
    InboundJim212-111-1111
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, jdubs ;

    You could merge it in power query.

    1.merge it

    2.expand name.

    3.merge it again.

    4.expand name.

    5.merge two columns.

     

    The final output is shown below:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8sxLyi/NS1HSUTIyNNI1NDLWNTE1M1eK1YlW8i8tQZaztDDXNTM1MQbLoWkzNARhQ6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Call Type" = _t, #"Phone Number" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Call Type", type text}, {"Phone Number", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Phone Number"}, #"Table (2)", {"Home Phone"}, "Table (2)", JoinKind.LeftOuter),
    #"Expanded Table (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table (2)", {"Name"}, {"Table (2).Name"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded Table (2)", {"Phone Number"}, #"Table (2)", {"Mobile Phone"}, "Table (2)", JoinKind.LeftOuter),
    #"Expanded Table (2)1" = Table.ExpandTableColumn(#"Merged Queries1", "Table (2)", {"Name"}, {"Table (2).Name.1"}),
    #"Merged Columns" = Table.CombineColumns(#"Expanded Table (2)1",{"Table (2).Name", "Table (2).Name.1"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged")
in
    #"Merged Columns"


    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • jdubs's avatar
    jdubs
    Icon for Helper V rankHelper V
    4 years ago

    Sorry guys, still struggling with this. There is another layer of complexity in that I am actually matching on two different tables. So ultimately I want to create a new table where the phone number in Table 1 matches any of the phone numbers listed in Tables 2 & 3. In SQL I could do this with a series of joins, but I am just not clear on how to achieve it using either the GUI or Power Query directly.

     

    Table 1 (Phone calls) - Phone Number

    Table 2 (Accounts) - Phone Number

    Table 3 (Contacts) - Phone Number & Mobile Phone

     

      • jdubs's avatar
        jdubs
        Icon for Helper V rankHelper V
        4 years ago

        Ashsih, hopefully this works or else I can try to upload a .pbix file

         

        Phone Call Table

        Call DirectionCall TimePhone Number
        Inbound8:13:00 AM212-999-0000
        Inbound8:17:00 AM718-444-1234
        Outbound8:38:00 AM800-888-8000
        Inbound9:10:00 AM718-555-5155
        Outbound9:50:00 AM718-444-4198
        Inbound10:10:00 AM212-222-2020
        Inbound10:14:00 AM212-666-6400
        Outbound10:25:00 AM800-111-1100

         

        Accounts Table

        NamePhone Number
        Bob's Painting718-555-5155
        Nails 'N Things212-666-6400
        Home Improvments, Inc.516-969-8000
        Roofs 4 U412-958-1212
        John's Diner718-444-4198
        Acme Tools800-111-1100

         

        Contacts Table

        NamePhone NumberMobile Phone
        Bob Jones212-999-1313212-999-0000
        Sam Smith718-444-1234 
        Sheila Gray 212-666-6400
        John Wilkens718-444-4198 
        Todd Roberts 917-111-3000
        Bill McGuinness212-222-2020212-222-2020

         

        Resulting Matched Calls Table

        Call Direction Call TimePhone NumberAccount NameAccount Phone NumberContact NameContact Phone NumberContact Mobile Phone
        Inbound8:13:00 AM212-999-0000  Bob Jones212-999-1313212-999-0000
        Inbound8:17:00 AM718-444-1234  Sam Smith718-444-1234 
        Outbound8:38:00 AM800-888-8000     
        Inbound9:10:00 AM718-555-5155Bob's Painting718-555-5155   
        Outbound9:50:00 AM718-444-4198John's Diner718-444-4198John Wilkens718-444-4198 
        Inbound10:10:00 AM212-222-2020  Bill McGuinness212-222-2020212-222-2020
        Inbound10:14:00 AM212-666-6400  Sheila Gray 212-666-6400
        Outbound10:25:00 AM800-111-1100Acme Tols800-111-1100