Additional column not showing in Power Query merge files from folder

[chriscorpion786]

Hi All,

I have 3 workbooks, in the first two workbooks columns are from A to D all smiliar, they are store names, in the third workbook, an additonal store has been added, so it is from A to E,
When merging all workbooks, from folder option in Power Query and combining all files, the new store column does not appear.
Is there an easy method , or fix for this. Can anyone help in this.

Many thanks,
Mustafa

 

[alansidman]

Need to see some sample data that is representative of your actual files. Suggest you upload via XL2BB so that it can be tested and analyzed.

 

[chriscorpion786]

Hi, I have captured the below, hope this helps. The last column which is "PAK" has been added in the third file, which is captured below.

03-March.csv
	B	C	D	E	F	G	H	I	J	K	L	M
1	Product Name	MOE	RAK	JIMMI	DCC	MCC	SHIN	SHJ	SAF	FUJ-CC	MAR	PAK
2	All-Purpose Bike Stand	$11,293.90	$2,323.02	$38,001.40	$243.60	$10,211.56	$5,956.16	$53,965.33	$30,152.57	$45,769.43	$43,305.56	$22,398
3	AWC Logo Cap	$6,684.37	$37,425.12	$665.50	$25,986.55	$1,400.25	$20,270.28	$25,529.62	$53,101.26	$7,327.48	$3,570.56	$87,920
4	Classic Vest, M	$19,578.50	$28,770.82	$3,999.42	$4,338.74	$29,467.02	$18,586.33	$3,476.42	$10,447.48	$134.96	$44,234.99	$74,282
5	Classic Vest, S	$46,774.88	$1,782.53	$3,061.78	$79,255.86	$53,587.72	$1,219.00	$9,452.36	$8,233.52	$1,490.11	$22,840.97	$75,208
6	Front Derailleur	$25,003.16	$16,839.76	$5,489.84	$1,639.74	$70,603.03	$20,661.15	$56,675.36	$14,701.28	$12,688.83	$17,917.56	$24,416
7	Full-Finger Gloves, M	$24,926.30	$29,732.23	$6,467.22	$13,200.69	$5,041.07	$21,719.60	$26,723.04	$68,409.08	$10,467.17	$5,027.72	$58,648
8	Full-Finger Gloves, S	$23,369.09	$33,034.86	$44,226.90	$37,101.45	$31,437.30	$7,810.30	$47,293.48	$9,598.09	$1,776.16	$36,524.38	$67,450
9	Half-Finger Gloves, L	$7,564.48	$38,095.62	$22,115.34	$69,210.43	$62,237.33	$2,973.39	$17,844.41	$30,509.45	$42,530.73	$32,250.84	$20,460
10	Half-Finger Gloves, M	$38,756.71	$56,893.58	$5,276.31	$6,943.55	$17,693.75	$11,497.14	$35,633.96	$25,769.78	$23,979.73	$38,821.67	$55,236
11	HL Bottom Bracket	$6,007.67	$13,751.08	$201.49	$17,496.91	$30,566.39	$17,176.33	$34,331.62	$19,546.24	$13,917.98	$635.64	$49,639
12	HL Crankset	$64,482.00	$34,652.80	$21,978.16	$6,066.02	$37,204.79	$33,803.84	$39,093.95	$18,227.62	$18,097.86	$43,574.80	$24,124
13	HL Fork	$20,205.71	$33,758.85	$57,227.31	$15,519.60	$52,779.89	$3,432.26	$6,807.56	$58,707.64	$4,218.97	$2,999.03	$71,167
03-March

 

[alansidman]

So, because you have only shown the result, am I to assume that the first two files were appended to each other and you wish to append this file to those? Or am I missing something here?

 

[chriscorpion786]

Hi, no this is not the result, its the third file to be appended. The new column is M, with "PAK" data added to this file.
The previous two files dont have column M.

 

[alansidman]

If I am understanding correctly then the following should work for you. Your file shown above is Table1. Tables 2 and 3 are the same file without the last column. I loaded each into the PQ Editor and appended 3 to 2 and 1 to the balance. It created a new column but did not summarize the data. To summarize the data I did a Group By. Here is the code for the last steps after they had been "merged/appended"

let
    Source = Table.Combine({Sheet3, Sheet2, Sheet1}),
    #"Grouped Rows" = Table.Group(Source, {"Product Name"}, {{"MOE Total", each List.Sum([MOE]), type nullable number}, {"RAK Total", each List.Sum([RAK]), type nullable number}, {"JIMMI Total", each List.Sum([JIMMI]), type nullable number}, {"DCC Total", each List.Sum([DCC]), type nullable number}, {"MCC Total", each List.Sum([MCC]), type nullable number}, {"SHIN Total", each List.Sum([SHIN]), type nullable number}, {"SHJ Total", each List.Sum([SHJ]), type nullable number}, {"SAF Total", each List.Sum([SAF]), type nullable number}, {"FUJ-CC Total", each List.Sum([#"FUJ-CC"]), type nullable number}, {"MAR Total", each List.Sum([MAR]), type nullable number}, {"PAK Total", each List.Sum([PAK]), type nullable number}})
in
    #"Grouped Rows"

Book6
	A	B	C	D	E	F	G	H	I	J	K	L
1	Product Name	MOE Total	RAK Total	JIMMI Total	DCC Total	MCC Total	SHIN Total	SHJ Total	SAF Total	FUJ-CC Total	MAR Total	PAK Total
2	All-Purpose Bike Stand	33881.7	6969.06	114004.2	730.8	30634.68	17868.48	161895.99	90457.71	137308.29	129916.68	22398
3	AWC Logo Cap	20053.11	112275.36	1996.5	77959.65	4200.75	60810.84	76588.86	159303.78	21982.44	10711.68	87920
4	Classic Vest, M	58735.5	86312.46	11998.26	13016.22	88401.06	55758.99	10429.26	31342.44	404.88	132704.97	74282
5	Classic Vest, S	140324.64	5347.59	9185.34	237767.58	160763.16	3657	28357.08	24700.56	4470.33	68522.91	75208
6	Front Derailleur	75009.48	50519.28	16469.52	4919.22	211809.09	61983.45	170026.08	44103.84	38066.49	53752.68	24416
7	Full-Finger Gloves, M	74778.9	89196.69	19401.66	39602.07	15123.21	65158.8	80169.12	205227.24	31401.51	15083.16	58648
8	Full-Finger Gloves, S	70107.27	99104.58	132680.7	111304.35	94311.9	23430.9	141880.44	28794.27	5328.48	109573.14	67450
9	Half-Finger Gloves, L	22693.44	114286.86	66346.02	207631.29	186711.99	8920.17	53533.23	91528.35	127592.19	96752.52	20460
10	Half-Finger Gloves, M	116270.13	170680.74	15828.93	20830.65	53081.25	34491.42	106901.88	77309.34	71939.19	116465.01	55236
11	HL Bottom Bracket	18023.01	41253.24	604.47	52490.73	91699.17	51528.99	102994.86	58638.72	41753.94	1906.92	49639
12	HL Crankset	193446	103958.4	65934.48	18198.06	111614.37	101411.52	117281.85	54682.86	54293.58	130724.4	24124
13	HL Fork	60617.13	101276.55	171681.93	46558.8	158339.67	10296.78	20422.68	176122.92	12656.91	8997.09	71167
Sheet2