Compare two tables using power query to create an output table displaying same value and the changes

[mxtun]

I have two excel tables table 1 and table 2. I want to compare both the tables using power query union all members and display the output in table 3

1. extra in table 1 (membership: termed member)

2. extra in table 2 (membership: new member)

3.  exist in both tables (membership: existing member)

4.  program changed between table 1 vs 2 (display change from table 1 to 2)

5.  program did NOT changed between table 1 vs 2 (display value from table 2)

6.  program only exist on table 2 (display value from table 2) for new members

7.  program exist only on table 1  (display value from table 1) for termed members

8.  level changed between table 1 vs 2 (display change from table 1 to 2)

9.  level did NOT changed between table 1 vs 2 (display value from table 2)

10.  level only exist on table 2 (display value from table 2) for new members

11.  level exist only on table 1  (display value from table 1) for termed members

pic is for reference

[Anonymous]

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

[Omid_Motamedise · Accepted solution]

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!