Transform Column into Multiple Rows based on cell with many delimiters

[Baileyleeg]

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.

[Mikelytics · Accepted solution]

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.

[Baileyleeg · Accepted solution]

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

[Mikelytics · Accepted solution]

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.

[Greg_Deckler]

@Baileyleeg So in Power Query, you would generally split that column and then unpivot the resulting columns.