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

Score big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount

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
August Power BI Update Carousel

Power BI Monthly Update - August 2025

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

August 2025 community update carousel

Fabric Community Update - August 2025

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