Forum Discussion

Chocbudda66's avatar
Chocbudda66
Copper Contributor
Apr 10, 2024

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

 PartnerambientfreshfrozenchilledDestination
1St vinnies125830Portland
2St Vinnies3020105Hamilton
3Salvos101010 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 DateCharity Partner NameKGProduce GroupPostcodeLGA
110.04.2024St vinnies12ambientPortland3305GLENELG SHIRE COUNCIL
110.04.2024St vinnies5freshPortland3305GLENELG SHIRE COUNCIL
110.04.2024St vinnies8frozenPortland3305GLENELG SHIRE COUNCIL
110.04.2024St vinnies30chilledPortland3305GLENELG SHIRE COUNCIL
210.04.2024St Vinnies30ambientHamilton3300SOUTHERN GRAMPIANS SHIRE COUNCIL
210.04.2024St Vinnies20freshHamilton3300SOUTHERN GRAMPIANS SHIRE COUNCIL
210.04.2024St Vinnies10frozenHamilton3300SOUTHERN GRAMPIANS SHIRE COUNCIL
210.04.2024St Vinnies5chilledHamilton3300SOUTHERN GRAMPIANS SHIRE COUNCIL
310.04.2024Salvos10ambientHamilton3300SOUTHERN GRAMPIANS SHIRE COUNCIL
310.04.2024Salvos10freshHamilton3300SOUTHERN GRAMPIANS SHIRE COUNCIL
310.04.2024Salvos10frozenHamilton3300SOUTHERN GRAMPIANS SHIRE COUNCIL
310.04.2024Salvos0chilledHamilton3300SOUTHERN GRAMPIANS SHIRE COUNCIL

 

 

Hamilton3300SOUTHERN GRAMPIANS SHIRE COUNCIL
Heywood3304GLENELG SHIRE COUNCIL
Portland3305GLENELG SHIRE COUNCIL

 

I'm not sure how to attach a sample workbook, sorry

  • Chocbudda66 

    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.

Share

Resources