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

[LoredanaC]

Hello all,

Can anyone help with the following scenerio?

My columns in Power Query look something like this:

 And I want to look like this:

Any help would be greatly appreciated! 

[dufoq3 · Accepted solution]

Hi @LoredanaC, try this

Result

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