Forum Discussion

mxtun's avatar
mxtun
New Member
1 year ago
Solved

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

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

 

 
  • 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

  • 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!

  • Anonymous's avatar
    Anonymous
    Not 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