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) ...
  • Omid_Motamedise's avatar
    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!