Forum Discussion

agajka's avatar
agajka
New Member
1 year ago
Solved

Power Query Merge - populate only first match

Hello All!

I'm looking for a way to join two tables in Power Query (source data is from SFDC: IPA_Opportunities + WorkOrder_Asset, key: Opportunity__c from Opportunity and WorkOrder objects), but only display details for the first match, considering that in the first table we have duplicate Opportunities corresponding to each IPA and they can't be removed.

 

Left table:

Opportunity NameIPA
Opp1IPA-0888
Opp1IPA-0889

 

Right table:

Opportunity NameWO Number
Opp10012345

 

Standard expected behavior of left-outer merge:

Opportunity NameIPAWO Number
Opp1IPA-08880012345
Opp1IPA-08890012345

 

Searched solution:

Opportunity NameIPAWO Number
Opp1IPA-08880012345
Opp1IPA-0889null

 

Thank you for your help!

Regards,

A.

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi agajka ,
    You can try the following code

    let
    Source1 = #"Table 1",
    Source2 = #"Table 2",
    GroupedTable = Table.Group(Source1, {"Opportunity"}, {{"AllData", each _, type table [Opportunity=nullable text, IPA=nullable text]}}),
    AddIndex = Table.TransformColumns(GroupedTable, {"AllData", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)}),
    #"Expanded AllData" = Table.ExpandTableColumn(AddIndex, "AllData", {"Index"}, {"Index"}),
    MergedTables = Table.NestedJoin(#"Expanded AllData", {"Opportunity"}, Source2, {"Opportunity"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(MergedTables, "Table2", {"Number"}, {"Table2.Number"}),
    AddCustom = Table.AddColumn(#"Expanded Table2", "Number", each if [Index] = 1 then [Table2.Number] else null),
    #"Removed Columns" = Table.RemoveColumns(AddCustom,{"Index", "Table2.Number"})
in
    #"Removed Columns"

    Final output

     

    Best regards,
    Albert He

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

2 Replies

  • Omid_Motamedise's avatar
    Omid_Motamedise
    Super User
    1 year ago

    Group the data on the left table based on first column and also use all row operation, then make the merge and after that create an we column based on the values match 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi agajka ,
    You can try the following code

    let
    Source1 = #"Table 1",
    Source2 = #"Table 2",
    GroupedTable = Table.Group(Source1, {"Opportunity"}, {{"AllData", each _, type table [Opportunity=nullable text, IPA=nullable text]}}),
    AddIndex = Table.TransformColumns(GroupedTable, {"AllData", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)}),
    #"Expanded AllData" = Table.ExpandTableColumn(AddIndex, "AllData", {"Index"}, {"Index"}),
    MergedTables = Table.NestedJoin(#"Expanded AllData", {"Opportunity"}, Source2, {"Opportunity"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(MergedTables, "Table2", {"Number"}, {"Table2.Number"}),
    AddCustom = Table.AddColumn(#"Expanded Table2", "Number", each if [Index] = 1 then [Table2.Number] else null),
    #"Removed Columns" = Table.RemoveColumns(AddCustom,{"Index", "Table2.Number"})
in
    #"Removed Columns"

    Final output

     

    Best regards,
    Albert He

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly