Power Query - Split Master Table into Multiple table as per value in a column (Dynamic)

ialwayscapital

New Member
Joined
Aug 18, 2009
Messages
18
Okay guys, I did ask this question in many forums but did not get any reply.

I have a salary sheet, where new data inserted every month. This is not a one time job, it needs to be done every month.

Salary Sheet looks like this. (image 1)

I want to print Salary sheet for each UNIT, with total below the table. Something like this (image 2)

With power query i can group table by UNIT and selecting ALL ROWS for all other data, This way i get a table for each unit. But i dont know how to load multiple tables from here.

I want multiple tables (one under another) in a same worksheet so that i can manipulate data & column widths etc to fit it in print area.

- As you can see (image 2), Unit name as Heading and whoever worked in that unit should be in that list then another heading of another Unit and whoever worked there.

- Header Row (Sr,Name, Bank & Acc No, etc etc) is not important. I could put this row at top and repeat it with print tiles.

- Units are DYNAMIC, may varies every other month.

- Space of 2 blank rows between two unit tables should be there. (Like shown in image 2)

Somehow I managed to achieve expected results by making helper sheet (see helper1 hidden sheet, where i assumed that maximum names in that unit would be 55) and then another worksheet with auto-filter remove blanks VBA. Its complicated see Sample Sheet.

I want some kind of formula based OR power query based solution for this. Its regular updating sheet, Units can increase or decrease, Number of names can increase or decrease every other month.

I am using office 365 (monthly targeted) fully updated version so all the dynamic array formulas available with me.

Feel free to play with sample sheet. Download Sample Sheet From Here

I think POWER QUERY can do this, but i dont know how.

Here is how i achieved the desired result.

1. Helper Sheet created. In a column i used formula =Unique(SR[Unit]) to get all the unique entries of UNIT column.
2. Created a table like design (Not excel official table, because dynamic array formulas are not supported in excel table) and used formula =FILTER(SR[[Unit]:[Status]],SR[Unit]=F2) Here F2 is the first row of Unique list mentioned in step 1.
3. I assumed that maximum NAMES in a table (mentioned in step 2) would be 55. (it can be more in future, thats why i am looking for some other solutions)
4. This way i created all the tables (about 50) and added TOTAL row manually.
5. Created another sheet (PRINT) and adjusted required data and column widths etc. Please UNHIDE HELPER1 SHEET in sample sheet for a better idea
6. Then used filter to remove blanks. Done
7. I used VBA code (to unprotect, refresh, filter blanks and then protect again) in PRINT sheet. PASSWORD is 911

Used tons of formulas and tricks to achieve desired result, but this makes my sheet bulky and slow. I am looking for an easy way to achieve the same. Now am trying hands in power query. Lets see. Any help would be great.
Thanks



 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Watch MrExcel Video

Forum statistics

Threads
1,130,176
Messages
5,640,614
Members
417,158
Latest member
jimmy1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top