Consolidate Details from different excel sheets (Invoices)

[sanket_sk]

Dear All,

I have created one folder in which I am dumping daily created invoices in excel format.

All these invoices have different names/sales details, I want to consolidate and prepare master documents out of these invoices, change is consumer & key details are above the billing data, I want to collect these details (From C4, C5,C7,E4,E5,E6 and so on), combined with item description and rest billing details, sharing a sample of Invoice and expected consolidated data for reference.

I need your guidance to make it happen using excel Power Query ( Office 365 enterprise version)

Sales invoice tracker1.xlsx
	A	B	C	D	E	F	G
1		Vijay Sales
2		Street Address		P: Phone Number		Email
3		City, State ZIP Code		F: Fax Number		Website
4		Bill To:	Contoso, Ltd	Phone:	432-555-0189	Invoice #:	3-456-2
5		Address:	567 Walnut Lane	Fax:	432-555-0123	Invoice Date:	04-01-22
6				Email:	someone@example.com
7		Invoice For:	Project 2
8		Item #	Description	Qty	Unit Price	Discount	Price
9		Z4567	Invoice 3-456-2 Data 1	39	$ 5.00	$ -	$ 195.00
10		Z4568	Invoice 3-456-2 Data 2	40	4.00	5.00	155.00
11		Z4569	Invoice 3-456-2 Data 3	30	6.00	7.00	173.00
12		Z4570	Invoice 3-456-2 Data 4	40	7.00	-	280.00
13		Z4571	Invoice 3-456-2 Data 5	10	4.00	-	40.00
14		Z4572	Invoice 3-456-2 Data 6	5	8.00	-	40.00
15		Z4573	Invoice 3-456-2 Data 7	70	6.00	-	420.00
16		Z4574	Invoice 3-456-2 Data 8	25	4.00	-	100.00
17		Z4575	Invoice 3-456-2 Data 9	5	7.00	3.00	32.00
18		Z4576	Invoice 3-456-2 Data 10	80	1.00	-	80.00
19		Z4577	Invoice 3-456-2 Data 11	65	7.00	-	455.00
20		Z4578	Invoice 3-456-2 Data 12	44	1.00	-	44.00
21
22						Invoice Subtotal	$ 2,014.00
23						Tax Rate	8.75%
24						Sales Tax	176.23
25						Other	-
26		Make all checks payable to Vijay Sales.				Deposit Received	-
27		Total due in <#> days. Overdue accounts subject to a service charge of <#>% per month.				TOTAL	$ 2,190.23
Invoice

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G22:G26	Expression	=MOD(ROW(),2)=1	text	NO
G22:G26	Expression	=MOD(ROW(),2)=0	text	NO
G9:G21	Expression	=MOD(ROW(),2)=0	text	NO
G9:G21	Expression	=MOD(ROW(),2)=1	text	NO
F22:F26,B9:F21	Expression	=MOD(ROW(),2)=0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
G4	Any value

Sales invoice tracker1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Bill To:	Address:	Phone:	Fax:	Email:	Invoice #:	Invoice Date:	Item #	Description	Qty	Unit Price	Discount	Price
2	Contoso, Ltd	567 Walnut Lane	432-555-0189	432-555-0123	someone@example.com	3-456-2	44565	Z4567	Invoice 3-456-2 Data 1	39	5	0	195
3	Contoso, Ltd	567 Walnut Lane	432-555-0189	432-555-0123	someone@example.com	3-456-2	44565	Z4568	Invoice 3-456-2 Data 2	40	4	5	155
4	Contoso, Ltd	567 Walnut Lane	432-555-0189	432-555-0123	someone@example.com	3-456-2	44565	Z4569	Invoice 3-456-2 Data 3	30	6	7	173
5	Contoso, Ltd	567 Walnut Lane	432-555-0189	432-555-0123	someone@example.com	3-456-2	44565	Z4570	Invoice 3-456-2 Data 4	40	7	0	280
6	Contoso, Ltd	567 Walnut Lane	432-555-0189	432-555-0123	someone@example.com	3-456-2	44565	Z4571	Invoice 3-456-2 Data 5	10	4	0	40
7	Contoso, Ltd	567 Walnut Lane	432-555-0189	432-555-0123	someone@example.com	3-456-2	44565	Z4572	Invoice 3-456-2 Data 6	5	8	0	40
8	Contoso, Ltd	567 Walnut Lane	432-555-0189	432-555-0123	someone@example.com	3-456-2	44565	Z4573	Invoice 3-456-2 Data 7	70	6	0	420
9	Contoso, Ltd	567 Walnut Lane	432-555-0189	432-555-0123	someone@example.com	3-456-2	44565	Z4574	Invoice 3-456-2 Data 8	25	4	0	100
10	Contoso, Ltd	567 Walnut Lane	432-555-0189	432-555-0123	someone@example.com	3-456-2	44565	Z4575	Invoice 3-456-2 Data 9	5	7	3	32
11	Contoso, Ltd	567 Walnut Lane	432-555-0189	432-555-0123	someone@example.com	3-456-2	44565	Z4576	Invoice 3-456-2 Data 10	80	1	0	80
12	Contoso, Ltd	567 Walnut Lane	432-555-0189	432-555-0123	someone@example.com	3-456-2	44565	Z4577	Invoice 3-456-2 Data 11	65	7	0	455
13	Contoso, Ltd	567 Walnut Lane	432-555-0189	432-555-0123	someone@example.com	3-456-2	44565	Z4578	Invoice 3-456-2 Data 12	44	1	0	44
Consolidated List