Power Query, comparing two tables, show changes

[hillandmichael]

Hi, 

I have two tables and I want to show any changes in table two from table one. For example:

1. The stroke can have more than one row of data 

2. Table 2, not all strokes will have made a change of hanger reference

3. Table 2, new stroke references can be seen (though these should be excluded. 

How do I get a table that matches the result table?

[wdx223_Daniel]

=let a=Table.Buffer(Table.Group(Table1,"Stroke",{"t",each [Hanger Reference]})) in #table({"Stroke","New Reference","Previous Reference"},List.TransformMany(Table.ToRows(Table.Group(Table2,"Stroke",{"t",each [Hanger Reference]})),each let r1=a{[Stroke=_{0}]}?[t]? ??{} in if List.ContainsAll(r1,_{1}) then {} else List.Zip({List.RemoveItems(_{1},r1),List.RemoveItems(r1,_{1})}),(x,y)=>{x{0}}&y))

[hillandmichael]

Thanks Daniel. will I add this as a query? 

[wdx223_Daniel]

just copy this code in your new step

[hillandmichael]

Sorry i am not following. Where would I add the code? Will this be a new measure or a new column? 

[AlexisOlson · Accepted solution]

One approach would be to start with Table 2, merge in Table 1 matching on Stroke, expand the Table 1 reference column, and then filter the resulting table with the rule [New Reference] <> [Previous Reference]. Note: this would give 123125 twice in the final table instead of having a blank cell.