Forum Discussion

zhoura345's avatar
zhoura345
Frequent Visitor
2 years ago
Solved

Help with Merging Two Tables to Get Desired Result

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!

Need Help

6 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      zhoura345
      Frequent Visitor
      2 years ago

      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. 

    • zhoura345's avatar
      zhoura345
      Frequent Visitor
      2 years ago

      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's avatar
        Ahmedx
        Icon for Super User rankSuper User
        2 years ago

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

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    2 years ago

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