Forum Discussion
Compare two tables using power query to create an output table displaying same value and the changes
- 1 year ago
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!
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!