Forum Discussion
Transform Column into Multiple Rows based on cell with many delimiters
Hello I'm hopeful someone can assist with transforming a product line that has multiple pack sizes in one column into multiple rows. I believe this can be done in Power Query but I have been unable to find the instructional video demostrating the steps required.
Current Input:
Product Name | Company | Brand | Constituent | Formulation | Pack Sizes |
TERBYNE XTREME 875 WG HERBICIDE | SYNGENTA AUST | SYNGENTA | terbuthylazine(875g/kg) | WDG | 10kg, 15kg, 20kg |
Desired Output:
Product Name | Company | Brand | Constituent | Formulation | Pack Sizes |
TERBYNE XTREME 875 WG HERBICIDE | SYNGENTA AUST | SYNGENTA | terbuthylazine(875g/kg) | WDG | 10kg |
TERBYNE XTREME 875 WG HERBICIDE | SYNGENTA AUST | SYNGENTA | terbuthylazine(875g/kg) | WDG | 15kg |
TERBYNE XTREME 875 WG HERBICIDE | SYNGENTA AUST | SYNGENTA | terbuthylazine(875g/kg) | WDG | 20kg |
Many thanks in advance for any assistance.
Hi Baileyleeg,
Please use the split column feature
Result
Best regards
Michael
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your kudos.
Thanks Greg_Deckler I appreciate your quick response. In the database there are 1000s of row items as follows. Would the split and unpivot work in this circumstance? Thanks
Input:
Product Name
Company
Brand
Constituent
Formulation
Pack Sizes
TERBYNE XTREME 875 WG HERBICIDE
SYNGENTA AUST
SYNGENTA
terbuthylazine(875g/kg)
WDG
10kg, 15kg, 20kg
FUHUA GLYPHOSATE 450 HERBICIDE
SICHUAN LESHAN
FUHUA
glyphosate as ipa(450g/L)
SL
5L, 10L, 20L, 110L, 200L, 1000L
Desired Output:
Product Name
Company
Brand
Constituent
Formulation
Pack Sizes
TERBYNE XTREME 875 WG HERBICIDE
SYNGENTA AUST
SYNGENTA
terbuthylazine(875g/kg)
WDG
10kg
TERBYNE XTREME 875 WG HERBICIDE
SYNGENTA AUST
SYNGENTA
terbuthylazine(875g/kg)
WDG
15kg
TERBYNE XTREME 875 WG HERBICIDE
SYNGENTA AUST
SYNGENTA
terbuthylazine(875g/kg)
WDG
20kg
FUHUA GLYPHOSATE 450 HERBICIDE
SICHUAN LESHAN
FUHUA
glyphosate as ipa(450g/L)
SL
5L
FUHUA GLYPHOSATE 450 HERBICIDE
SICHUAN LESHAN
FUHUA
glyphosate as ipa(450g/L)
SL
10L
FUHUA GLYPHOSATE 450 HERBICIDE
SICHUAN LESHAN
FUHUA
glyphosate as ipa(450g/L)
SL
20L
FUHUA GLYPHOSATE 450 HERBICIDE
SICHUAN LESHAN
FUHUA
glyphosate as ipa(450g/L)
SL
110L
FUHUA GLYPHOSATE 450 HERBICIDE
SICHUAN LESHAN
FUHUA
glyphosate as ipa(450g/L)
SL
200L
FUHUA GLYPHOSATE 450 HERBICIDE
SICHUAN LESHAN
FUHUA
glyphosate as ipa(450g/L)
SL
1000L
3 Replies
Baileyleeg So in Power Query, you would generally split that column and then unpivot the resulting columns.
Hi Baileyleeg,
Please use the split column feature
Result
Best regards
Michael
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your kudos.
Thanks Greg_Deckler I appreciate your quick response. In the database there are 1000s of row items as follows. Would the split and unpivot work in this circumstance? Thanks
Input:
Product Name
Company
Brand
Constituent
Formulation
Pack Sizes
TERBYNE XTREME 875 WG HERBICIDE
SYNGENTA AUST
SYNGENTA
terbuthylazine(875g/kg)
WDG
10kg, 15kg, 20kg
FUHUA GLYPHOSATE 450 HERBICIDE
SICHUAN LESHAN
FUHUA
glyphosate as ipa(450g/L)
SL
5L, 10L, 20L, 110L, 200L, 1000L
Desired Output:
Product Name
Company
Brand
Constituent
Formulation
Pack Sizes
TERBYNE XTREME 875 WG HERBICIDE
SYNGENTA AUST
SYNGENTA
terbuthylazine(875g/kg)
WDG
10kg
TERBYNE XTREME 875 WG HERBICIDE
SYNGENTA AUST
SYNGENTA
terbuthylazine(875g/kg)
WDG
15kg
TERBYNE XTREME 875 WG HERBICIDE
SYNGENTA AUST
SYNGENTA
terbuthylazine(875g/kg)
WDG
20kg
FUHUA GLYPHOSATE 450 HERBICIDE
SICHUAN LESHAN
FUHUA
glyphosate as ipa(450g/L)
SL
5L
FUHUA GLYPHOSATE 450 HERBICIDE
SICHUAN LESHAN
FUHUA
glyphosate as ipa(450g/L)
SL
10L
FUHUA GLYPHOSATE 450 HERBICIDE
SICHUAN LESHAN
FUHUA
glyphosate as ipa(450g/L)
SL
20L
FUHUA GLYPHOSATE 450 HERBICIDE
SICHUAN LESHAN
FUHUA
glyphosate as ipa(450g/L)
SL
110L
FUHUA GLYPHOSATE 450 HERBICIDE
SICHUAN LESHAN
FUHUA
glyphosate as ipa(450g/L)
SL
200L
FUHUA GLYPHOSATE 450 HERBICIDE
SICHUAN LESHAN
FUHUA
glyphosate as ipa(450g/L)
SL
1000L
