Handling Columns With Multiple Values in Excel

I have an Excel table that looks like this:

I would like to have each fruit in a different row like so:

Any idea how to achieve that?

Thanks.

Solution 1:

Select the Range- go to Data- From Sheet- open Power Query editor- select favorite fruits column and under Home tab select Split Column- Select By Delimiter- select favorite fruits.1 and favorite fruits.2 columns- go to Transform- Unpivot Columns- Remove Attribute column- change the Value name to favorite fruit- Close and load to...:

Solution 2:

You could use Power Query, available in Windows Excel 2010+ and Office 365, to split the Favorites column on the Comma into Rows.

To use Power Query

• Select some cell in your Data Table
• Data => Get&Transform => from Table/Range
• When the PQ Editor opens: Home => Advanced Editor
• Make note of the Table Name in Line 2
• Paste the M Code below in place of what you see
• Change the Table name in line 2 back to what was generated originally.
• Read the comments and explore the Applied Steps to understand the algorithm

M Code

let

//Read in the data
//Be sure to change Table Name in next line to the actual name in your workbook
    Source = Excel.CurrentWorkbook(){[Name="Fruits"]}[Content],

//set the data type
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"id", Int64.Type}, 
        {"favorite fruits", type text}
        }),

//Split the Favorites column by the comma, into Rows!
    #"Split Column by Delimiter" = Table.ExpandListColumn(
        Table.TransformColumns(#"Changed Type", {
            {"favorite fruits", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), 
            let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "favorite fruits"),

//Trim the contents of the column to remove leading spaces (when delimiter was <comma> <space>
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"favorite fruits", Text.Trim, type text}})
in
    #"Trimmed Text"