Forum Discussion
Help splitting table rows out to multiple rows in new table
I need to split individual rows of an existing table out to new rows in a new table (for export)and don;t know how to even begin this!?
I'm happy to do the research if someone is kind enough to point me in the direction I need to head!
This is the current table
Partner | ambient | fresh | frozen | chilled | Destination | |
1 | St vinnies | 12 | 5 | 8 | 30 | Portland |
2 | St Vinnies | 30 | 20 | 10 | 5 | Hamilton |
3 | Salvos | 10 | 10 | 10 | Hamilton |
Each row needs to be transposed to 4 new rows individualised on the "produce group" (also pulling in data from a third "lookup" table
I need it transposed to this:
Transaction # | Transaction Date | Charity Partner Name | KG | Produce Group | Postcode | LGA | |
1 | 10.04.2024 | St vinnies | 12 | ambient | Portland | 3305 | GLENELG SHIRE COUNCIL |
1 | 10.04.2024 | St vinnies | 5 | fresh | Portland | 3305 | GLENELG SHIRE COUNCIL |
1 | 10.04.2024 | St vinnies | 8 | frozen | Portland | 3305 | GLENELG SHIRE COUNCIL |
1 | 10.04.2024 | St vinnies | 30 | chilled | Portland | 3305 | GLENELG SHIRE COUNCIL |
2 | 10.04.2024 | St Vinnies | 30 | ambient | Hamilton | 3300 | SOUTHERN GRAMPIANS SHIRE COUNCIL |
2 | 10.04.2024 | St Vinnies | 20 | fresh | Hamilton | 3300 | SOUTHERN GRAMPIANS SHIRE COUNCIL |
2 | 10.04.2024 | St Vinnies | 10 | frozen | Hamilton | 3300 | SOUTHERN GRAMPIANS SHIRE COUNCIL |
2 | 10.04.2024 | St Vinnies | 5 | chilled | Hamilton | 3300 | SOUTHERN GRAMPIANS SHIRE COUNCIL |
3 | 10.04.2024 | Salvos | 10 | ambient | Hamilton | 3300 | SOUTHERN GRAMPIANS SHIRE COUNCIL |
3 | 10.04.2024 | Salvos | 10 | fresh | Hamilton | 3300 | SOUTHERN GRAMPIANS SHIRE COUNCIL |
3 | 10.04.2024 | Salvos | 10 | frozen | Hamilton | 3300 | SOUTHERN GRAMPIANS SHIRE COUNCIL |
3 | 10.04.2024 | Salvos | 0 | chilled | Hamilton | 3300 | SOUTHERN GRAMPIANS SHIRE COUNCIL |
Hamilton | 3300 | SOUTHERN GRAMPIANS SHIRE COUNCIL |
Heywood | 3304 | GLENELG SHIRE COUNCIL |
Portland | 3305 | GLENELG SHIRE COUNCIL |
I'm not sure how to attach a sample workbook, sorry
- OliverScheurichGold Contributor
This can be done with Power Query. In the attached file you can add data to the blue dynamic tables. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
The data layout in the screenshot and in the attached file is for illustration. You can place the green result table to the right of the blue tables or in another worksheet. The blue tables can be arranged horizontally as well.