Power Query Merge - populate only first match

[agajka]

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 Name	IPA
Opp1	IPA-0888
Opp1	IPA-0889

Right table:

Opportunity Name	WO Number
Opp1	0012345

Standard expected behavior of left-outer merge:

Opportunity Name	IPA	WO Number
Opp1	IPA-0888	0012345
Opp1	IPA-0889	0012345

Searched solution:

Opportunity Name	IPA	WO Number
Opp1	IPA-0888	0012345
Opp1	IPA-0889	null

Thank you for your help!

Regards,

A.

[Anonymous · Accepted solution]

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

[Omid_Motamedise]

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