Working with Multiple headers in Power Query

[Anonymous]

Hi All,

The below data set is of a particular branch (there are more than 100 branches in our company)extracted from Tally. I am finding it diffucult to work with multiple headers in the balance sheet and every branch has different line items and finally i am supposed to consolidate all the branches together to give the final report.

Please suggest me a solution to how segregate the line items under each (Common) header so that i can add other branches under the same headers.

Sales Accounts (Common Header)	xxxxxx.00
Branch Transfer Outward IntraState	xxxxx.00
Sales Grease IntraState	xxxx.00
Sales - Local
Sales Oil Intrastate	xxxx0.00
Sales Stabilizers IntraState
Sales_Interstate	xxxx0.00
Sales_Intrastate	xxxxx01.62
Sales_Oil_Intrastate
Service VAS Local
Direct Incomes (Common header)	xxxx7.00
VAS - Spares
Discount Received - Principal	xxxx7.00
Packing & Forwarding Charges (Sales)	xxx0.00
Pragati Spare Commission
		xxxxxx98.62
Cost of Sales  (Common header)	xxxxx4.97
Opening Stock	xxxxx16.49
Add: Purchase Accounts	xxxxx3.90
Less: Closing Stock	xxxx7.42
	xxxx2.97
Direct Expenses( Common Header)	xxxx2.00
Franchisee Spare Commission
Freight & Cartridge (GST)	xxxx2.00
Freight Charges - URD	xxxx0.00
Packing & Forwarding Charges - Inward
Gross Profit  (Common Header)	xxxxxxx3.65
Income Statement:
Indirect Incomes(Common Header)	xxxxxxx9.00
AMC
Marketing Income	xxxxx5.00
Other Income
Service Income	xxxxxxx4.00
Tech Centre Income
VAS Service
Scrap Sales
		xxxx2.65
Indirect Expenses  (Common Header)	xxxx8.64
Franchisee - AMC Commission	xxx10.00
Labour Charges
Loading & Unloading Charges	xxxx0.00
Reconditioning Expenses
Rounded Off
Tools & Spares for Servicing
Tools & Spares for Servicing - Under Warranty	xxxx8.64
ADMINISTRATION EXPENSES (Common Header)	xxxxx61.81
Branding Expenses
Communication Expenses	xxxx0.00
Information Technology Expenses
Local Conveyance & Car Hire charges	xxx68.00
Office Expenses	xxx82.51
Rent	xxxxx0.00
Vehicle Expenses - Petrol
Vehicle Expenses - Tyre
Vehicle Maintanance
Audit Related Expenses
Books News Paper & Periodicals	xxx4.00
Consumable Tools
Courier & Postage Charges	xx5.00
Customer Training Expenses - Tech Centre	xxxx0.00
Data Card & Internet Expenses	xxx6.00
Electricity & Water Charges
Happay Card Maintenance Charges
Insurance Charges - Vehicles
Internal Meeting Expenses	xxx5.00
ISO Expenses	xxx8.00
Legal & Professional Charges	xxx0.00
Mobile Expenses	xxxx1.12
Outstation Travel Expenses - Marketing
Outstation Travel Expenses - Meeting & Conference	xxxx5.00
Outstation Travel Expenses - Service	xxx75.00
Outstation Travel Expenses - Training	xxxx96.00
Printing & Stationery Expenses	xxx82.00
Repair & Maintenance Expenses	xxxx0.00
Short & Excess Recovery	xx1.09
Toll & Parking Charges
Vehicle Expenses Maintenance - Bike - Admin
Vehicle Expenses Maintenance - Bike - Marketing	xxxx1.00
Vehicle Expenses Maintenance - Bike - Service	xxxx5.00
Vehicle Expenses Maintenance - Car - Marketing	xxxx7.00
Vehicle Expenses Maintenance - Car - Service	xx20.00
Vehicle Expenses Petrol - Bike - Admin	xxxx0.00
Vehicle Expenses Petrol - Bike - Marketing	xxxx1.00
Vehicle Expenses Petrol - Bike - Service	xxxx7.98
Vehicle Expenses Petrol - Car - Marketing	xxxx40.40
Vehicle Expenses Petrol - Car - Service	xxxx50.71
DEPRECIATION (Common Header)
Employees Remuneration & Benefits	xxxx08.00
Contribution to Providend & Other Funds	xxxx3.00
Staff Welfare Expenses	xxxx0.00
L.T.A.	xxxx4.00
Medical Allowance	xxxx2.00
Mobile Allowance	xxxx00.00
Pay & Salary	xxxx19.00
Shoe Allowance	xxx00.00
Vehicle Allowance	xxx0.00
Finance Expenses	xx.90
Bank Charges	xx.90
Bank Charges -MSF
Nett Profit (Common Header)	xxxxx.30

Thank you.

Warm Regards(Shrikant)

[Stachu]

I would have a single table listing the common headers, like this

Sales Accounts

Direct Incomes

Cost of Sales

Direct Expenses

Gross Profit

Indirect Incomes

Indirect Expenses

ADMINISTRATION EXPENSES

DEPRECIATION

Nett Profit

I would then append the data from all the branches, merge it with this table and use the filter out all unnecessary rows 

[Anonymous · Accepted solution]

I cleaned one sheet using power query  ( created a single column with the common headers while doing so )and used the steps to create a function. Applied the function on other sheets and then appended . Worked for me

---

@Stachu wrote:

I would have a single table listing the common headers, like this

Sales Accounts

Direct Incomes

Cost of Sales

Direct Expenses

Gross Profit

Indirect Incomes

Indirect Expenses

ADMINISTRATION EXPENSES

DEPRECIATION

Nett Profit

I would then append the data from all the branches, merge it with this table and use the filter out all unnecessary rows 

---

.

Regards