Forum Discussion
Merged table - Match on two potential columns?
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.
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
Hi,
Share some data and show the expected result.
Ashsih, hopefully this works or else I can try to upload a .pbix file
Phone Call Table
Call Direction Call Time Phone Number Inbound 8:13:00 AM 212-999-0000 Inbound 8:17:00 AM 718-444-1234 Outbound 8:38:00 AM 800-888-8000 Inbound 9:10:00 AM 718-555-5155 Outbound 9:50:00 AM 718-444-4198 Inbound 10:10:00 AM 212-222-2020 Inbound 10:14:00 AM 212-666-6400 Outbound 10:25:00 AM 800-111-1100 Accounts Table
Name Phone Number Bob's Painting 718-555-5155 Nails 'N Things 212-666-6400 Home Improvments, Inc. 516-969-8000 Roofs 4 U 412-958-1212 John's Diner 718-444-4198 Acme Tools 800-111-1100 Contacts Table
Name Phone Number Mobile Phone Bob Jones 212-999-1313 212-999-0000 Sam Smith 718-444-1234 Sheila Gray 212-666-6400 John Wilkens 718-444-4198 Todd Roberts 917-111-3000 Bill McGuinness 212-222-2020 212-222-2020 Resulting Matched Calls Table
Call Direction Call Time Phone Number Account Name Account Phone Number Contact Name Contact Phone Number Contact Mobile Phone Inbound 8:13:00 AM 212-999-0000 Bob Jones 212-999-1313 212-999-0000 Inbound 8:17:00 AM 718-444-1234 Sam Smith 718-444-1234 Outbound 8:38:00 AM 800-888-8000 Inbound 9:10:00 AM 718-555-5155 Bob's Painting 718-555-5155 Outbound 9:50:00 AM 718-444-4198 John's Diner 718-444-4198 John Wilkens 718-444-4198 Inbound 10:10:00 AM 212-222-2020 Bill McGuinness 212-222-2020 212-222-2020 Inbound 10:14:00 AM 212-666-6400 Sheila Gray 212-666-6400 Outbound 10:25:00 AM 800-111-1100 Acme Tols 800-111-1100 Here is the .pbix file. Obviously it doesn't include the desired output becuase that's what I'm trying to figure out. But this screenshot shows what I am looking for.
Green cells indicate where a phone number matches. Red cells indicate no match.
Link to 'Matched Calls.pbix': https://we.tl/t-CSs8lsXvpd
Thank you Ashsih. Would you be able to explain how you came to this? My concern was that if I joined the Accout table first and then merged again that I would be missing potential matches against the Contact table.