Forum Discussion
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.
Thanks in advance!
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 AppendedThanks a lot, works fine, much appreciated!
And also, thanks for guidance on usable data.
3 Replies
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 AppendedThanks a lot, works fine, much appreciated!
And also, thanks for guidance on usable data.
You're welcome. Just one remark: you should mark as solution post with solution 😉