Forum Discussion
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...
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.
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.