Forum Discussion
Compare two tables using power query to create an output table displaying same value and the changes
You can solve this problem by merging or appending the tables.
in the below code I used appending which is faster. so just copy the code and past it into the advance editor and see the steps.
let Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLMSa3ILAYykpOBhE9+OZD0SswrTSyqVIrViVYyAqlJSczFrcIYyA9ILUktAtLOzr5A0iMzPQNNEZCLQCABUyAjKDE5IzUHyHAB6fJNTckszUXTZwbkOxUlpuC23hzIDy5JLUvFoSQWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, Program = _t, Level = _t, Month = _t]), Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLMSa3ILAYynH2BhG9qSmZpLpDhlppUVJpYVKkUqxOtZARSl5IIEk9OBhI++eXoSoyBAgGpJalFIJOcsasxAQoEJVbiUWEKVpGckZoDZLiAHOSRmZ6BrsoMKOBUlJgCczS6QbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, Program = _t, Level = _t, Month = _t]), Append = Table1 & Table2, Function=(a)=>Text.Combine(List.Distinct(a)," to "), #"Grouped Rows" = Table.Group(Append, {"ID", "Name"}, {{"Month", each List.Last(_[Month])},{"Program Change", each Function(_[Program])},{"Level Change", each Function(_[Level])},{"Membership", each if List.Count(_[ID])=2 then "Existing" else if List.Contains(Table1[ID],_[ID]{0}) then "Term" else "New"}}), #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([ID] <> "")) in #"Filtered Rows"it results in the below table
If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos.
Thank you!
2 Replies
- Omid_MotamediseSuper User
You can solve this problem by merging or appending the tables.
in the below code I used appending which is faster. so just copy the code and past it into the advance editor and see the steps.
let Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLMSa3ILAYykpOBhE9+OZD0SswrTSyqVIrViVYyAqlJSczFrcIYyA9ILUktAtLOzr5A0iMzPQNNEZCLQCABUyAjKDE5IzUHyHAB6fJNTckszUXTZwbkOxUlpuC23hzIDy5JLUvFoSQWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, Program = _t, Level = _t, Month = _t]), Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLMSa3ILAYynH2BhG9qSmZpLpDhlppUVJpYVKkUqxOtZARSl5IIEk9OBhI++eXoSoyBAgGpJalFIJOcsasxAQoEJVbiUWEKVpGckZoDZLiAHOSRmZ6BrsoMKOBUlJgCczS6QbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, Program = _t, Level = _t, Month = _t]), Append = Table1 & Table2, Function=(a)=>Text.Combine(List.Distinct(a)," to "), #"Grouped Rows" = Table.Group(Append, {"ID", "Name"}, {{"Month", each List.Last(_[Month])},{"Program Change", each Function(_[Program])},{"Level Change", each Function(_[Level])},{"Membership", each if List.Count(_[ID])=2 then "Existing" else if List.Contains(Table1[ID],_[ID]{0}) then "Term" else "New"}}), #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([ID] <> "")) in #"Filtered Rows"it results in the below table
If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos.
Thank you!
- AnonymousNot applicable
Hi mxtun ,
Thanks for Omid_Motamedise's reply!
mxtun It looks as if Omid_Motamedise's reply will solve your problem, Have you solved your problem? If solved please mark the reply in this post which you think is helpful as a solution to help more others facing the same problem to find a solution quickly, thank you very much!
Best Regards,
Dino Tao