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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
LoredanaC
Frequent Visitor

Splitting cell data into rows, delimited by "alt+enter" in Power Query

Hello all,

Can anyone help with the following scenerio?

 

My columns in Power Query look something like this:

LoredanaC_2-1711608070872.png

 

 And I want to look like this:

 

LoredanaC_1-1711608046986.png

Any help would be greatly appreciated! 

1 ACCEPTED SOLUTION
dufoq3
Super User
Super User

Hi @LoredanaC, try this

 

Result

dufoq3_0-1711610341197.png

 

v1

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WiskLKUpMLskvislT0lFyS81LKYnJC0vMKSlKjMlzTixOVYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Machinery = _t, Type = _t]),
    ToList = Table.TransformColumns(Source, List.Transform({"Machinery", "Type"}, (colName)=> {colName, each Text.SplitAny(_, "#(lf), #(cr)") } )),
    Ad_Merged = Table.AddColumn(ToList, "Merged", each List.Transform(List.Zip({ [Machinery], [Type] }), (x)=> Text.Combine(x, "|")), type list),
    RemovedColumns = Table.RemoveColumns(Ad_Merged,{"Machinery", "Type"}),
    ExpandedMerged = Table.ExpandListColumn(RemovedColumns, "Merged"),
    SplitColumnByDelimiter = Table.SplitColumn(ExpandedMerged, "Merged", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Machinery", "Type"}),
    TrimmedText = Table.TransformColumns(SplitColumnByDelimiter,{{"Machinery", Text.Trim, type text}}),
    ReplacedValue = Table.ReplaceValue(TrimmedText,"",null,Replacer.ReplaceValue,{"Machinery"}),
    FilledDown = Table.FillDown(ReplacedValue,{"Machinery"}),
    FilledUp = Table.FillUp(FilledDown,{"Machinery"})
in
    FilledUp

 

 

v2 (won't work properly if you have more columns than these two. In that case query needs to be updated)

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WiskLKUpMLskvislT0lFyS81LKYnJC0vMKSlKjMlzTixOVYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Machinery = _t, Type = _t]),
    Transformed = Table.FromRows(List.TransformMany(Table.ToRows(Source),
        each List.Transform(List.Skip(_), (a)=> List.Select(Text.SplitAny(a, "#(lf), #(cr)"), (b)=> Text.Trim(b) <> "")),
        (x,y)=> {Text.Trim(x{0}, {"#(lf)", "#(cr)"})} & {y} ), {"Machinery", "Type"}),
    ExpandedType = Table.ExpandListColumn(Transformed, "Type")
in
    ExpandedType

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

1 REPLY 1
dufoq3
Super User
Super User

Hi @LoredanaC, try this

 

Result

dufoq3_0-1711610341197.png

 

v1

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WiskLKUpMLskvislT0lFyS81LKYnJC0vMKSlKjMlzTixOVYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Machinery = _t, Type = _t]),
    ToList = Table.TransformColumns(Source, List.Transform({"Machinery", "Type"}, (colName)=> {colName, each Text.SplitAny(_, "#(lf), #(cr)") } )),
    Ad_Merged = Table.AddColumn(ToList, "Merged", each List.Transform(List.Zip({ [Machinery], [Type] }), (x)=> Text.Combine(x, "|")), type list),
    RemovedColumns = Table.RemoveColumns(Ad_Merged,{"Machinery", "Type"}),
    ExpandedMerged = Table.ExpandListColumn(RemovedColumns, "Merged"),
    SplitColumnByDelimiter = Table.SplitColumn(ExpandedMerged, "Merged", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Machinery", "Type"}),
    TrimmedText = Table.TransformColumns(SplitColumnByDelimiter,{{"Machinery", Text.Trim, type text}}),
    ReplacedValue = Table.ReplaceValue(TrimmedText,"",null,Replacer.ReplaceValue,{"Machinery"}),
    FilledDown = Table.FillDown(ReplacedValue,{"Machinery"}),
    FilledUp = Table.FillUp(FilledDown,{"Machinery"})
in
    FilledUp

 

 

v2 (won't work properly if you have more columns than these two. In that case query needs to be updated)

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WiskLKUpMLskvislT0lFyS81LKYnJC0vMKSlKjMlzTixOVYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Machinery = _t, Type = _t]),
    Transformed = Table.FromRows(List.TransformMany(Table.ToRows(Source),
        each List.Transform(List.Skip(_), (a)=> List.Select(Text.SplitAny(a, "#(lf), #(cr)"), (b)=> Text.Trim(b) <> "")),
        (x,y)=> {Text.Trim(x{0}, {"#(lf)", "#(cr)"})} & {y} ), {"Machinery", "Type"}),
    ExpandedType = Table.ExpandListColumn(Transformed, "Type")
in
    ExpandedType

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors