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:  ...
Need Help
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.