Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!The Power BI Data Visualization World Championships is back! It's time to submit your entry. Live 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
The Power BI Data Visualization World Championships is back! It's time to submit your entry.
| User | Count |
|---|---|
| 16 | |
| 9 | |
| 8 | |
| 7 | |
| 7 |