Power Query Advice on Import and Merge Files in a Folder

[VBA learner ITG]

Hi all,

I am a novice and learning as i go along and I was wondering if i could tap into your expertise.

I have a folder with about 100 of these files to impot which i am trying to merge into 1 master file.
Each workbook has a similar format in terms of structure. However, the store number and associated address values rows can be different on each workbook.
From column L onwards in each workbook they can differ and can also be the same and different amount of columns.
Each workbook i am trying to import are not in a Table or Range.

Can anyone advise/give guidance on how they would Import and merge workbooks together in Power Query?

SPR516 1 of 1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI
1	Project Ref	SPR516	Project Description	P13-21 Themed Kit - Central
2											Brief Description	P13-21 - Headline Deal - Diet Coke and Zero - Full Barker - 89x203	P13-21 - Headline Deal - Diet Coke and Zero - Stack Card - 297x210	P13-21 - Headline Deal - Coca Cola - Full Barker - 89x203	P13-21 - Headline Deal - Coca Cola - Stack Card - 297x210	P13-21 - Headline Deal - Walkers - Full Barker - 89x203	P13-21 - Headline Deal - Walkers - Stack Card - 297x210	P13-21 - Headline Deal - Walkers Baked, Sunbites and Snack a Jack - Full Barker - 89x203	P13-21 - Headline Deal - Walkers Baked, Sunbites and Snack a Jack - Stack Card - 297x210	P13-21 - Headline Deal - Nescafe Azera - Full Barker - 89x203	P13-21 - Headline Deal - Nescafe Azera - Stack Card - 297x210	P13-21 - Headline Deal - Tetley Tea Bags - Full Barker - 89x203	P13-21 - Headline Deal - Tetley Tea Bags - Stack Card - 297x210	P13-21 - Headline Deal - Princes Tuna - Full Barker - 89x203	P13-21 - Headline Deal - Princes Tuna - Stack Card - 297x210	P13-21 - Headline Deal - Hellmans Mayo - Full Barker - 89x203	P13-21 - Headline Deal - Hellmans Mayo - Stack Card - 297x210	P13-21 - Headline Deal - Bold 2 in 1 - Full Barker - 89x203	P13-21 - Headline Deal - Bold 2 in 1 - Stack Card - 297x210	P13-21 - Headline Deal - Flora - Full Barker - 89x203	P13-21 - Headline Deal - McCain Chips - Full Barker - 89x203	P13-21 - Headline Deal - Mayo, Walkers and Diet Coke - Poster 711x508	P13-21 - Headline Deal - Mayo, Walkers and Diet Coke - Poster (CERTAS) - 1016x762	P13-21 - Headline Deal - Mayo, Walkers and Diet Coke - Poster (RONTEC) - 1189x841	P13-21 - Headline Deal - Mayo, Walkers and Diet Coke - Poster (ROADCHEF) - 841x594	P13-21 - Headline Deal - Nescafe, Tetley - Poster 711x508	P13-21 - Headline Deal - Nescafe, Tetley - Poster (CERTAS) - 1016x762	P13-21 - Headline Deal - Nescafe, Tetley - Poster (RONTEC) - 1189x841	P13-21 - Headline Deal - Nescafe, Tetley - Poster (ROADCHEF) - 841x594	P13-21 - Headline Deal - Flora and McCains - Poster 711x508	P13-21 - Headline Deal - Flora and McCains - Poster (CERTAS) - 1016x762	P13-21 - Headline Deal - Flora and McCains - Poster (RONTEC) - 1189x841	P13-21 - Headline Deal - Flora and McCains - Poster (ROADCHEF) - 841x594	P13-21 - Healthy Value - Header - 490x895	P13-21 - Healthy Value - Stripping - 38x1000	P13-21 - Healthy Value - Stripping - 73x990	P13-21 - Healthy Value Secondary Deal - Bud Light - Barker - 89x203	P13-21 - Healthy Value Secondary Deal - Mr Kipling - Barker - 89x203	P13-21 - Healthy Value Secondary Deal - Naked Smoothie - Barker - 89x203	P13-21 - Healthy Value Secondary Deal - Hartley's - Barker - 89x203	P13-21 - Healthy Value Secondary Deal - Fibre One - Barker - 89x203	P13-21 - Frozen Meal Deal - Barker - 89x203	P13-21 - Frozen Meal Deal - Stripping - 38x1000	P13-21 - Frozen Meal Deal - 73x990	P13-21 - Frozen Meal Deal - Poster - 711x508	P13-21 - Frozen Meal Deal - Poster (CERTAS) - 1016x762	P13-21 - Frozen Meal Deal - Poster (RONTEC) - 1189x841	P13-21 - Frozen Meal Deal - Poster (ROADCHEF) - 841x594	P13-21 - Fresh For Less - Bus Stop - 111x113	P13-21 - Fresh For Less - Stripping - 38x1000	P13-21 - Fresh For Less - Stripping - 73x990
3											Total (inc Overs)	2430	2430	2430	2430	2430	2430	1740	1740	2080	1784	2430	2430	2430	2430	2430	2430	2430	2080	2080	2080	1310	40	27	13	1310	40	27	13	1310	40	27	13	2430	5928	8664	1317	2430	2430	2430	2430	1130	1788	3390	485	40	27	13	1830	1788	3714
4											Total Allocations	2430	2430	2430	2430	2430	2430	1740	1740	2080	1784	2430	2430	2430	2430	2430	2430	2430	2080	2080	2080	1310	40	27	13	1310	40	27	13	1310	40	27	13	2430	5928	8664	1317	2430	2430	2430	2430	1130	1788	3390	485	40	27	13	1830	1788	3714
5											Overs	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
6	Stock Receipt Ref
7	Store Number	Store Name	Address Line 1	Address Line 2	City or Town	County	Country	Post Code	Region / Area	Location Type	Trading Format	SPR516/5859	SPR516/5860	SPR516/5861	SPR516/5862	SPR516/5863	SPR516/5864	SPR516/5865	SPR516/5866	SPR516/5867	SPR516/5868	SPR516/5869	SPR516/5870	SPR516/5871	SPR516/5872	SPR516/5873	SPR516/5874	SPR516/5875	SPR516/5876	SPR516/5877	SPR516/5878	SPR516/5879	SPR516/5880	SPR516/5881	SPR516/5882	SPR516/5883	SPR516/5884	SPR516/5885	SPR516/5886	SPR516/5887	SPR516/5888	SPR516/5889	SPR516/5890	SPR516/6301	SPR516/6302	SPR516/6303	SPR516/6304	SPR516/6305	SPR516/6306	SPR516/6307	SPR516/6308	SPR516/6309	SPR516/6310	SPR516/6311	SPR516/6312	SPR516/6313	SPR516/6314	SPR516/6315	SPR516/6316	SPR516/6317	SPR516/6318
8	000000	Test Address 1	Hygeia Building	66 - 68 College Road	Harrow	Middlesex		HA1 1BE		N/A	Central Office	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2				2				2				2	12	12	2	2	2	2	2	4	12	12	2				4	12	12
9	N/A1	Test Address 2	N/A	N/A	N/A	N/A		NA1 1NA		N/A	RDC Themed Kit Allocations	187	187	187	187	187	187	187	187	187	187	187	187	187	187	187	187	187	187	187	187	187				187				187				187		1122		187	187	187	187	374		1122	187				374		1122
10	N/A2	Test Address 3	N/A		N/A	N/A		NA1 1NA		N/A	RDC Themed Kit Allocations	280	280	280	280	280	280			280	280	280	280	280	280	280	280	280	280	280	280													280	1680		280	280	280	280	280
11	N/A3	Test Address 4	N/A		N/A	N/A		NA1 1NA		N/A	RDC Themed Kit Allocations	280	280	280	280	280	280			280	280	280	280	280	280	280	280	280	280	280	280													280	1680			280	280	280	280
12	N/A4	Test Address 5	N/A		N/A	N/A		NA1 1NA		N/A	RDC Themed Kit Allocations	260	260	260	260	260	260	260	260	260		260	260	260	260	260	260	260	260	260	260	260				260				260				260	1560			260	260	260	260	520	1560	1560	260				520	1560
13	N/A5	Test Address 6	N/A		N/A	N/A		NA1 1NA		N/A	RDC Themed Kit Allocations	36	36	36	36	36	36	36	36	36		36	36	36	36	36	36	36	36	36	36	36				36				36				36	216			36	36	36	36	72	216	216	36				72	216
14	N/A6	Test Address 7	N/A		N/A	N/A		NA1 1NA		N/A	RDC Themed Kit Allocations	310	310	310	310	310	310	310	310			310	310	310	310	310	310	310																310		1860		310	310	310	310								620		1860
15	N/A7	Test Address 8	N/A		N/A	N/A		NA1 1NA		N/A	RDC Themed Kit Allocations	40	40	40	40	40	40	40	40			40	40	40	40	40	40	40																40		240		40	40	40	40								80		240
16	N/A9	Test Address 9	N/A		N/A	N/A		NA1 1NA		N/A	National Accounts Allocations	27	27	27	27	27	27	27	27	27	27	27	27	27	27	27	27	27	27	27	27			27				27				27		27		162	27	27	27	27	27	54		162			27		54		162
17	N/A10	Test Address 10	N/A		N/A	N/A		NA1 1NA		N/A	National Accounts Allocations	13	13	13	13	13	13	13	13	13	13	13	13	13	13	13	13	13	13	13	13				13				13				13	13		78	13	13	13	13	13	26		78				13	26		78
18	N/A25	Test Address 11	N/A		N/A	N/A		NA1 1NA		N/A	National Accounts Allocations	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40	40		40				40				40			40		240	40	40	40	40	40	80		240		40			80		240
19	N/A56	Test Address 12	N/A		N/A	N/A		NA1 1NA		N/A	RDC Themed Kit Allocations	600	600	600	600	600	600	600	600	600	600	600	600	600	600	600	600	600	600	600	600	600				600				600				600		3600	600	600	600	600	600
Allocations

Cell Formulas
Range	Formula
L3:BI3	L3	=SUM(L4:L5)
L4:BI4	L4	=SUM(L8:L21)

 

[AlanY]

check out this YT video to see if that help

 

[VBA learner ITG]

check out this YT video to see if that help

Thank you I will watch this video.