Forum Discussion
Compare Tables to Find Different Rows
I have two tables one has old data and the other has latest data.
I need to compare both and identify numbers that needs update.
Each number can have 1 to 50 unique descriptions within each cell separated by a line feed in any order.
The string length of the each unique description can vary from 4 to 350 characters.
If possible, I need Removed and Added description details for each number in the comparison table along with the status.
The below screenshot does not cover every scenario it gives a general idea how the comparison output would look like.
Solution Excel file uploaded to https://1drv.ms/x/s!Akd5y6ruJhvhuWM_E7x4impYGs_w?e=xOVN1N
The query used for Table1
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Numbers", type text}, {"Description", type text}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Numbers"}, Table2, {"Numbers"}, "Table2", JoinKind.LeftOuter), #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Description"}, {"Description.1"}), #"Added Custom" = Table.AddColumn(#"Expanded Table2", "Removed", each Text.Combine(List.Difference(Text.Split([Description],"#(lf)"),Text.Split([Description.1],"#(lf)")),"#(lf)"), type text), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Added", each Text.Combine(List.Difference(Text.Split([Description.1],"#(lf)"),Text.Split([Description],"#(lf)")),"#(lf)"), type text), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Description.1"}), #"Replaced Value" = Table.ReplaceValue(#"Removed Columns","","None",Replacer.ReplaceValue,{"Removed", "Added"}), #"Added Custom2" = Table.AddColumn(#"Replaced Value", "Custom", each if [Removed]="None" and [Added]="None" then "No Change" else "Update") in #"Added Custom2"
1 Reply
Solution Excel file uploaded to https://1drv.ms/x/s!Akd5y6ruJhvhuWM_E7x4impYGs_w?e=xOVN1N
The query used for Table1
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Numbers", type text}, {"Description", type text}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Numbers"}, Table2, {"Numbers"}, "Table2", JoinKind.LeftOuter), #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Description"}, {"Description.1"}), #"Added Custom" = Table.AddColumn(#"Expanded Table2", "Removed", each Text.Combine(List.Difference(Text.Split([Description],"#(lf)"),Text.Split([Description.1],"#(lf)")),"#(lf)"), type text), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Added", each Text.Combine(List.Difference(Text.Split([Description.1],"#(lf)"),Text.Split([Description],"#(lf)")),"#(lf)"), type text), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Description.1"}), #"Replaced Value" = Table.ReplaceValue(#"Removed Columns","","None",Replacer.ReplaceValue,{"Removed", "Added"}), #"Added Custom2" = Table.AddColumn(#"Replaced Value", "Custom", each if [Removed]="None" and [Added]="None" then "No Change" else "Update") in #"Added Custom2"