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

[PQRookie]

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.

Thanks in advance!

  

[dufoq3 · Accepted solution]

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 · Accepted solution]

Thanks a lot, works fine, much appreciated!

And also, thanks for guidance on usable data.

[dufoq3]

You're welcome. Just one remark: you should mark as solution post with solution 😉

[dufoq3 · Accepted solution]

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