Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
dreck
Frequent Visitor

Transform Table to Split Field with Multiple Entries Separated By Return

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:

Column1Column2Column3
FoodFruitApple
Orange
FoodFruitBanana
Pear
Grapes
FoodVegetableLettuce
FoodVegetableSpinach
Broccoli

 

 

To a table like this:

Column1Column2Column3
FoodFruitApple
FoodFruitOrange
FoodFruitBanana
FoodFruitPear
FoodFruitGrapes
FoodVegetableLettuce
FoodVegetableSpinach
FoodVegetableBroccoli

 

Any help would be greatly appreciated! 

1 ACCEPTED SOLUTION
FreemanZ
Super User
Super User

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:

FreemanZ_0-1699069491825.png

 

 

you may also refer to articles like:

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

 

View solution in original post

2 REPLIES 2
FreemanZ
Super User
Super User

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:

FreemanZ_0-1699069491825.png

 

 

you may also refer to articles like:

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

 

Exactly what I needed, thanks for the help!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.