Forum Discussion

ajsingh's avatar
ajsingh
Frequent Visitor
8 years ago

how to combile two tables based on condition

Hi All,   i was working on one scenario where i come across a condition, where i need to join two table like this
Need Help
ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
8 years ago

I would probably do it like so:

1) Add an index-column to your left table as a key

2) Remove duplicates on sno

3) Merge (Left-Outer) - that will match just one row from the lookup-table

4) Merge that back to your source-table on key (with Source on the left): So the removed dups will not get a lookup-match

ajsingh's avatar
ajsingh
Frequent Visitor
8 years ago

Hey ImkeF thanks for the help.

till third step i did, but in 4th step, i am not able to understand, how to do it .

Some screenshots would be a great help.

  • ImkeF's avatar
    ImkeF
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Please paste this code into the advanced editor and follow each step:

     

    let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWBsJzALCMgy1kpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Added Index" = Table.AddIndexColumn(Table1, "Index", 0, 1),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIsLclXitWJVjICcpwTi5RiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Added Index1" = Table.AddIndexColumn(Table2, "Index", 0, 1),
    RemoveDups = Table.Distinct(#"Added Index", {"Column1"}),
    #"Merged Queries" = Table.NestedJoin(RemoveDups,{"Column1"},#"Added Index1",{"Column1"},"Removed Duplicates",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(#"Merged Queries", "Removed Duplicates", {"Column2"}, {"Column2.1"}),
    Custom1 = #"Added Index",
    #"Merged Queries1" = Table.NestedJoin(Custom1,{"Index"},Expand,{"Index"},"Custom1",JoinKind.LeftOuter),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Merged Queries1", "Custom1", {"Column2.1"}, {"Column2.1"})
in
    #"Expanded Custom1"

     

    The 4th move is step "Merged Queries1": There you merge the existing table ("Custom1") with a table that has been generated in an earlier step ("Expand").

     

    I've shown how to reference an existing step in this litte screencast here: https://www.youtube.com/watch?v=looCm3cbINw