Transform Table to Split Field with Multiple Entries Separated By Return

[dreck]

I'm working with a messy dataset and can't figure out how to transform my table to make it usable.  Below is a sample. Column 3

has multiple entries in the same cell.

I'm trying to go from a table like this:

Column1	Column2	Column3
Food	Fruit	Apple Orange
Food	Fruit	Banana Pear Grapes
Food	Vegetable	Lettuce
Food	Vegetable	Spinach Broccoli

To a table like this:

Column1	Column2	Column3
Food	Fruit	Apple
Food	Fruit	Orange
Food	Fruit	Banana
Food	Fruit	Pear
Food	Fruit	Grapes
Food	Vegetable	Lettuce
Food	Vegetable	Spinach
Food	Vegetable	Broccoli

Any help would be greatly appreciated! 

[FreemanZ · Accepted solution]

hi @dreck ,

This is typical split column to rows, in Power Query.

The M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dYoxCoAwEAT/crWfMIU2goJgY1KccdFAuIQz/t+UgsgUO7CzrtSltFNDnd6h1G1zjrAyKssBcs0nMCwVKxNYrfTKGde7W3Cg8BZRfUApt8ffPecg7E8rRpP3KQZy7gE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column3", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column3")
in
    #"Split Column by Delimiter"

it worked like:

you may also refer to articles like:

https://www.excelcampus.com/powerquery/split-into-rows/

[dreck]

Exactly what I needed, thanks for the help!