Forum Discussion

PQRookie's avatar
PQRookie
Frequent Visitor
2 years ago
Solved

Power Query amend a table with rows from another one based on conditions,

Hi, quite new to PQ and wonder if I can get some help on this one? I need a new table with all rows from Table1 and all rows from Table2 with IDs that exist in Table1. Should then look as Table3. ...
  • dufoq3's avatar
    2 years ago

    Hi PQRookie, for future requests, provide sample data in usable format (as table), so we can copy/paste. If you don't know how to do it, read my note below.

     

    let
        Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtE3MAQiJR2lRCA2VIrVwSJshF3YGFXYCCiUBMQm2IVNsQubKcXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, ID = _t, Val = _t]),
        Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtA3MNM3NFDSUUoEYiAjVgdNPAkkbogpngwSN8IUB5tjjCJuZAozxwRTHGyOKaY42BwzHOaY4zDHAoc5lijixjB/GRlgioPMMQL6NxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, ID = _t, Val = _t]),
        // Filterd only IDs from Table1
        Table2Filtered = Table.SelectRows(Table2, each List.Contains(List.Distinct(Table1[ID]), [ID])),
        Appended = Table.Combine({Table1, Table2Filtered})
    in
        Appended
  • PQRookie's avatar
    2 years ago

    Thanks a lot, works fine, much appreciated!

    And also, thanks for guidance on usable data.