Using Master Parts List for Multiple files

[dominical]

I have a parts list that will be used in multiple tables. I have a separate file for each location and a file with the overall summary of inventory, if we buy a new item I want to be able to add it to the summary sheet at any row (as they grouped by similar parts) and it populate the other files for each location. Only the first two columns of each row is identical the others are formulas or data that need to adjust to the insertion of a new row. I can get the first two rows to update but the other columns remain unchanged which means they are now referring to the wrong part and wrong formula. Below is an example of one of the tables, they will all have a varying number of columns but the same number of rows. Please let me know if this is possible and how.

 

[jdellasala]

Starting with separate tables for each site's inventory (they can be in different Workbooks):

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Item No.	Description	Site 1		Item No.	Description	Site 2		Item No.	Description	Site 3
2	5127	Dolly Transporting			5127	Dolly Transporting			5127	Dolly Transporting
3	266	Truss Nylon Sling 6'	1		266	Truss Nylon Sling 6'			266	Truss Nylon Sling 6'
4	6121	Truss Lower/Lift Jack 4' MKII	6		6121	Truss Lower/Lift Jack 4' MKII			6121	Truss Lower/Lift Jack 4' MKII
5	17	Aluma Beam 21'	98		17	Aluma Beam 21'	34		17	Aluma Beam 21'	5
6	71	Aluma Beam 20'	2		71	Aluma Beam 20'	1		71	Aluma Beam 20'
7	69	Aluma Beam 19'			69	Aluma Beam 19'			69	Aluma Beam 19'
8	11	Aluma Beam 18'	418		11	Aluma Beam 18'	34		11	Aluma Beam 18'	28
9	68	Aluma Beam 17'	1		68	Aluma Beam 17'			68	Aluma Beam 17'
10	12	Aluma Beam 16'	26		12	Aluma Beam 16'	47		12	Aluma Beam 16'	61
11	67	Aluma Beam 15'	28		67	Aluma Beam 15'	42		67	Aluma Beam 15'
12	14	Aluma Beam 14'	22		14	Aluma Beam 14'	23		14	Aluma Beam 14'	2
13	66	Aluma Beam 13'	107		66	Aluma Beam 13'	1		66	Aluma Beam 13'	1
14	16	Aluma Beam 12'	187		16	Aluma Beam 12'	2		16	Aluma Beam 12'	9
15	63	Aluma Beam 11'	7		63	Aluma Beam 11'	13		63	Aluma Beam 11'	1
16	15	Aluma Beam 10'-6"	5		15	Aluma Beam 10'-6"	5		15	Aluma Beam 10'-6"	1
17	62	Aluma Beam 10'	28		62	Aluma Beam 10'	1		62	Aluma Beam 10'	8
18	85	Aluma Beam 9'	2		85	Aluma Beam 9'	1		85	Aluma Beam 9'
19	115	Aluma Beam 8'	22		115	Aluma Beam 8'	7		115	Aluma Beam 8'	5
20	113	Aluma Beam 7'	16		113	Aluma Beam 7'	12		113	Aluma Beam 7'	2
21	112	Aluma Beam 6'			112	Aluma Beam 6'			112	Aluma Beam 6'
22	111	Aluma Beam 5'	1		111	Aluma Beam 5'			111	Aluma Beam 5'
23	109	Aluma Beam 4'			109	Aluma Beam 4'			109	Aluma Beam 4'
24	4046	A-Clamp Assembly	117		4046	A-Clamp Assembly	1,486		4046	A-Clamp Assembly	100
25	6161	HD#2 Post Shores	2,248		6161	HD#2 Post Shores	628		6161	HD#2 Post Shores	104
Sheet1

Then append the 3 tables, then group the Item# and Description columns and sum each site, and add to get the total:

let
    Source = Table.Combine({Site1, Site2, Site3}),
    GroupedRows = Table.Group(Source, {"Item No.", "Description"}, {{"Site 1", each List.Sum([Site 1]), type nullable number}, {"Site 2", each List.Sum([Site 2]), type nullable number}, {"Site 3", each List.Sum([Site 3]), type nullable number}}),
    InsertedTotalInventory = Table.AddColumn(GroupedRows, "Total Inventory", each List.Sum({[Site 1], [Site 2], [Site 3]}), type number)
in
    InsertedTotalInventory

Book1
	M	N	O	P	Q	R
1	Item No.	Description	Site 1	Site 2	Site 3	Total Inventory
2	5127	Dolly Transporting
3	266	Truss Nylon Sling 6'	1			1
4	6121	Truss Lower/Lift Jack 4' MKII	6			6
5	17	Aluma Beam 21'	98	34	5	137
6	71	Aluma Beam 20'	2	1		3
7	69	Aluma Beam 19'
8	11	Aluma Beam 18'	418	34	28	480
9	68	Aluma Beam 17'	1			1
10	12	Aluma Beam 16'	26	47	61	134
11	67	Aluma Beam 15'	28	42		70
12	14	Aluma Beam 14'	22	23	2	47
13	66	Aluma Beam 13'	107	1	1	109
14	16	Aluma Beam 12'	187	2	9	198
15	63	Aluma Beam 11'	7	13	1	21
16	15	Aluma Beam 10'-6"	5	5	1	11
17	62	Aluma Beam 10'	28	1	8	37
18	85	Aluma Beam 9'	2	1		3
19	115	Aluma Beam 8'	22	7	5	34
20	113	Aluma Beam 7'	16	12	2	30
21	112	Aluma Beam 6'
22	111	Aluma Beam 5'	1			1
23	109	Aluma Beam 4'
24	4046	A-Clamp Assembly	117	1486	100	1703
25	6161	HD#2 Post Shores	2248	628	104	2980
Sheet1

Please use XL2BB when posting data.

 

[dominical]

Thank you for your help but I don't think I explained my problem clearly, I have multiple workbooks with tables that all use power query to get the Item no. and Description from the summary sheet. Each of those tables have many adjacent columns of shipments and returns and those get added up and sent back to summary and that all works fine. The problem is we periodically buy new products and they are grouped by category so when adding new products I would like to be able to insert in the middle of the table. When I do that it only updates the first two columns and shifts everything down except now the remaining columns no longer correspond to the correct product as they are not shifted down to match. It is kind off difficult to explain so I made an illustration.