This time we’re going bigger than ever. Fabric, Power BI, SQL, AI and more. We're covering it all. You won't want to miss it.
Learn moreDid you hear? There's a new SQL AI Developer certification (DP-800). Start preparing now and be one of the first to get certified. Register now
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)
Solved! Go to 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
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
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
Check out the April 2026 Power BI update to learn about new features.
If you have recently started exploring Fabric, we'd love to hear how it's going. Your feedback can help with product improvements.
A new Power BI DataViz World Championship is coming this June! Don't miss out on submitting your entry.