Help with Merging Two Tables to Get Desired Result

[zhoura345]

I have two datasets (one with 5k+ rows, the other 1k+ rows) already loaded in the Power Query Editor. For simplification here are two sample tables to show what I'm looking to do: 

Table 1: 

Table 2: 

I need a way to merge the two such that I get this result: 

None of the default joins available in the Merge Queries seems to be able to get me this. The Left Outer join gives me all values in the Volume column since for every Trip# in Table1, there is a match in Table2. 

What can I do to get this desired result? Thank you so much!

[Ahmedx]

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

[Ahmedx · Accepted solution]

pls try this

[zhoura345]

Thank you! This worked very well. If you don't mind me asking, my original dataset has many columns, and they seem to be gone after the GroupBy action. Is there a way to return them back? 

[Ahmedx]

at the “Extended” step, you must check all columns

[ryan_mayu]

@zhoura345 

try to create index column in both table and create rank column

= Table.Group(#"Added Index","Trip",{"a", each Table.AddRankColumn(_,"rank",{"Index",1})})

then when you merge two table, both matching trip column and rank column

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRUitXBoIwglDE6FQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Trip = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Trip", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    Custom1 = Table.Group(#"Added Index","Trip",{"a", each Table.AddRankColumn(_,"rank",{"Index",1})}),
    #"Expanded a" = Table.ExpandTableColumn(Custom1, "a", {"rank"}, {"rank"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded a", {"Trip", "rank"}, #"Table (2)", {"Trip", "rank"}, "Table (2)", JoinKind.LeftOuter),
    #"Expanded Table (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table (2)", {"Volumn"}, {"Volumn"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table (2)",{"rank"})
in
    #"Removed Columns"

pls see the attachment below

[zhoura345]

Thank you for taking the time to explain the steps. This was so easy to follow and I was able to replicate the result with the simple dataset. However, when I attempted to use the same method on my original big dataset, it returned all null values.