Splitting column details

[mweber87]

I have this data set. There are different types of pizzas. There is a column that is labeled ingredients. And each row lists all the ingredients for that pizza on one line. Example 

bbq_ckn

The Barbecue Chicken Pizza

Chicken

Barbecued Chicken, Red Peppers, Green Peppers, Tomatoes, Red Onions, Barbecue Sauce

However, I would like to seperate the ingredients so they show up on different rows. For example:

pizza_type_id	name	category	ingredients
bbq_ckn	The Barbecue Chicken Pizza	Chicken	Barbecued Chicken
bbq_ckn	The Barbecue Chicken Pizza	Chicken	Red Peppers
bbq_ckn	The Barbecue Chicken Pizza	Chicken	Green Peppers
bbq_ckn	The Barbecue Chicken Pizza	Chicken	Tomatoes
bbq_ckn	The Barbecue Chicken Pizza	Chicken	Red Onion
bbq_ckn	The Barbecue Chicken Pizza	Chicken	Barbecued Sauce

I am new to Power Bi and DAX so any help would be greatly apprecaited!

[Daryl-Lynch-Bzy · Accepted solution]

Hi @mweber87 - There is a Split Column by Delimiter option in Power Query that will help.  Please watch the following example:  Power Query - Split a cell into rows | Get delimited lists into a usable format | Excel Off The Grid...

Many thanks

Daryl

[ImkeF]

Sound like you want to pivot on the new "ingredients"-column?
Not sure what you want to see in the columns with the ingredients actually?

[ImkeF · Accepted solution]

Hi @mweber87 ,
check the column, go to the transform tab, choose "Split Column", use "Comma" as a delimiter, expand the Advanced options and select "Rows":

[mweber87]

Yes thank you, @ImkeF  that worked! Is there now a way for me to create a list for all the pizza's that use each ingredient?

[Daryl-Lynch-Bzy · Accepted solution]

Hi @mweber87 - There is a Split Column by Delimiter option in Power Query that will help.  Please watch the following example:  Power Query - Split a cell into rows | Get delimited lists into a usable format | Excel Off The Grid...

Many thanks

Daryl