Extract and Pivot Text String Column

[JB]

Hello, how would I go about turning the table on the left into the table on the right? Is there a way to extract the values from a cell and turn them into new rows? 

Many thanks. 

[jennratten · Accepted solution]

Hello - yes, this can be done by splitting the fruits column into new rows, like this:

SCRIPT

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyEkt1lEISE0sglDJGanFSjpKxkqxOtFKTol5QAiUcM5Pzs8rLQEpycxLTQTrAqoyUYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fruits = _t, Quantity = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fruits", type text}, {"Quantity", Int64.Type}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Fruits", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Fruits"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Fruits", type text}})
in
    #"Changed Type1"

RESULT

[JB]

Perfect, thanks!

[jennratten]

You're welcome!