Forum Discussion

frei17's avatar
frei17
Regular Visitor
4 years ago
Solved

Merge two tables: Right on Left and append all unique rows from Left

Hello,   I have two tables:   "Left" (Data from the previous week's report):   Column1Column2Column3Index apple Adam 12 3 banana Arnold 8 4 lemon Sally 9 5 Pear Linda 1 ...
General Comment
Tips and Tricks
Tutorial Requests
  • wdx223_Daniel's avatar
    wdx223_Daniel
    4 years ago

    all from my guess

    let
    Left = Excel.CurrentWorkbook(){[Name="Left"]}[Content],
    Right = Excel.CurrentWorkbook(){[Name="Right"]}[Content],
    Custom1 = let
                 col=Table.ColumnNames(Left),
                 LeftAntiGrp=Table.Group(Table.NestedJoin(Left,col,Right,col,"n",JoinKind.LeftAnti),"Column1",{"n",each _}),
                 RightGrp=Table.Group(Right,"Column1",{"n",each _})
              in
                 Table.FromRecords(
                                   List.TransformMany(
                                                      Table.ToRecords(LeftAntiGrp),
                                                      each let
                                                              a=RightGrp{[Column1=[Column1]]}?[n]?
                                                           in
                                                              if a=null then Table.ToRecords([n])
                                                              else List.Transform(
                                                                                  List.Zip(List.Transform(
                                                                                                          {[n],a},
                                                                                                          Table.ToRecords
                                                                                                         )
                                                                                          ),
                                                                                  each if _{1}=null then _{0} else _{0}&_{1}
                                                                                 ),
                                                      (x,y)=>y
                                                     ),
                                   col
                                  )
in
    Custom1

     

  • lbendlin's avatar
    lbendlin
    4 years ago

    this is truly bananas... 🙂

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSiwoyElV0lFyTEnMBVKGRkqxOtFKSYl5QAgSLsrLz0kBMizA4jmpufl5QF5wYk5OJZC2BIsGpCYWATk+mXkpID2GyIKuRZnJIDEDZEFnoEqQdkMzpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    Left = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    Right = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSiwoyElV0lFyTEnMBVLm5uZKsTrRSkmJeUAIFPDNTM5ITM0BsoyMwTI5qbn5eUBucGJOTiWQtgSLBqQmFgE5XvkZIDlDZDEFMI6NBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Added Index" = Table.AddIndexColumn(Right, "Index", 0, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index", {"Column1", "Column2", "Column3"}, Left, {"Column1", "Column2", "Column3"}, "Appended Query", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom", each Table.RowCount([Appended Query])),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 0)),
    #"Appended Query" = Table.Combine({#"Filtered Rows", Table.SelectRows(Left,(k)=> not List.Contains(#"Added Index"[Index],k[Index]))})
in
    #"Appended Query"
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
4 years ago

what happened to banana/Arnold?

  • frei17's avatar
    frei17
    Regular Visitor
    4 years ago

    the primary key 'banana' has got its values updated  in the RIght and the row has become 'banana/Michael', if that makes sense.

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      4 years ago

      That is bananas 🙂  .  There is an issue with  Pear/Eric and Pear/null too.  How do you know these two rows are related?  By row index?

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSiwoyElV0lFyTEnMBVKGRkqxOtFKSYl5QAgSLsrLz0kBMizA4jmpufl5QF5wYk5OJZC2BIsGpCYWATk+mXkpID2GyIKuRZnJIDEDZEFnoEqQdkMzpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    Left = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    Right = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSiwoyElV0lFyTEnMBVLm5uZKsTrRSkmJeUAIFPDNTM5ITM0BsoyMwTI5qbn5eUBucGJOTiWQtgSLBqQmFgE5XvkZIDlDZDEFMI6NBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Added Index" = Table.AddIndexColumn(Right, "Index", 0, 1, Int64.Type),
    #"Appended Query" = Table.Combine({#"Added Index", Table.SelectRows(Left,(k)=> not List.Contains(#"Added Index"[Index],k[Index]))})
in
    #"Appended Query"
      • frei17's avatar
        frei17
        Regular Visitor
        4 years ago

        Your code does not exclude 'lemon/Sally', but it should.

        As for the row relation - similarily to how Excel interprets it while merging Right on Left. It picks up the first occurence (or by higher index, or by rule of precedence), all the rest are neglected (I am guessing). Anyhow, no rows must get missed, only duplicates need to go out.