Extract differences from different workbooks

[d365b]

Hi everyone,

I hope you are well. I have a spreadsheet that evolves every week. I used Power Query and imported the workbooks produced the past weeks, basically copying the query and simply modifying the source. Is there a way to remove alla common data (what remains unchanged basically) and output only the differences (what’s new)?

Many thanks

 

[JGordon11]

Maybe. It would depend on how your data is structured and how the changes manifest.

 

[d365b]

Maybe. It would depend on how your data is structured and how the changes manifest.

Hey JGordon. Each workbook, once filtered has 3500+ rows. Most of the rows remain unaltered. The changes could manifest as a new value in a row or a new row all together.

 

[JGordon11]

let
    tbl1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    rows = Table.ToRecords(tbl1),
    tbl3 = Table.SelectRows(tbl2, each not List.Contains(rows,_))
in
    tbl3

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2	Table1					Table2					Query Output
3
4	ID	Column2	Column3	Column4		ID	Column2	Column3	Column4		ID	Column2	Column3	Column4
5	1	21	23	11		1	21	23	11		6	B	34	20
6	2	28	1	26		2	28	1	26		8	12	C	16
7	3	22	2	13		3	22	2	13		10	17	18	D
8	4	31	10	35		4	31	10	35		11	G	E	H
9	5	30	24	8		5	30	24	8		12	F	D	B
10	6	15	34	20		6	B	34	20
11	7	5	9	14		7	5	9	14
12	8	12	40	16		8	12	C	16
13	9	7	6	38		9	7	6	38
14	10	17	18	33		10	17	18	D
15						11	G	E	H
16						12	F	D	B
17
Sheet1

 

[d365b]

let
    tbl1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    rows = Table.ToRecords(tbl1),
    tbl3 = Table.SelectRows(tbl2, each not List.Contains(rows,_))
in
    tbl3

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2	Table1					Table2					Query Output
3
4	ID	Column2	Column3	Column4		ID	Column2	Column3	Column4		ID	Column2	Column3	Column4
5	1	21	23	11		1	21	23	11		6	B	34	20
6	2	28	1	26		2	28	1	26		8	12	C	16
7	3	22	2	13		3	22	2	13		10	17	18	D
8	4	31	10	35		4	31	10	35		11	G	E	H
9	5	30	24	8		5	30	24	8		12	F	D	B
10	6	15	34	20		6	B	34	20
11	7	5	9	14		7	5	9	14
12	8	12	40	16		8	12	C	16
13	9	7	6	38		9	7	6	38
14	10	17	18	33		10	17	18	D
15						11	G	E	H
16						12	F	D	B
17
Sheet1

Hey JGordon, thank you for your suggestion! Considering the size of such tables, I do not have them as tables on the current workbook. Can the formula you shared be modified accordingly?

Thanks

 

[JGordon11]

Just change the tbl1 and tbl2 steps to retrieve your tables from their locations.

 

[d365b]

Just change the tbl1 and tbl2 steps to retrieve your tables from their locations.

Right JGordon,

I have got this far

let
    lastWeek = #"2022_01",
    thisWeek = #"2022_02",
    trace = Table.Column(Table.RemoveMatchingRows(thisWeek, Table.ToRecords(lastWeek)), "ProdNr")
in
    trace

So now I have a list of Product Numbers that have changed but now I would like to combine a table looking up the ProdNr in both lastWeek and thisWeek tables. I tried this for a single table but, despite not giving me an error, it keeps processing and never returns a table

tbl = Table.SelectRows(lastWeek , each List.ContainsAny( {[ProdNr]}, trace) )

Any idea what am I doing wrong?

 

[JGordon11]

It may just be an inefficient way of going about it (in fact I'm pretty sure it is). If you can run it on a much smaller version of your datasets (say ~50 rows) and it works in a few seconds, then it is just a very cpu intensive algorithm for large data sets. Would need to rethink how to make it more efficient.

 

[JGordon11]

The code I provided in post #4 is very slow for large datasets. I tested the code below on a test sample with 3500 rows and it was almost instantaneous.

let
    tbl1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    tcn2 = Table.ColumnNames(tbl2),
    renames = List.Transform(Table.ColumnNames(tbl1), each {_, _ & "1"}),
    tbl1r = Table.RenameColumns(tbl1, renames),
    tbl3 = Table.Join(tbl1r,Table.ColumnNames(tbl1r), tbl2, tcn2, JoinKind.RightAnti ),
    Result = Table.RemoveColumns(tbl3, Table.ColumnNames(tbl1r))
in
    Result