Forum Discussion

Lori_C's avatar
Lori_C
Frequent Visitor
8 years ago

Split text string column values into multiple rows at a position relative to a delimiter

Hello, I need to split the values in the Notes column (see BEFORE) into a separate row for each note (see AFTER).  A new note begins when a timestamp occurs in the text string.  The timestamp follows...
Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

See if this works:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pVTbcpswFPyVM+5LOmOnQpib8iSDbOQgiZGEM0yap04/of/fgw0UZzBxmjcNg3ZXu3vO6+sq3tKE0tV6FSRZFqVxFuKZJBsSbigJUkhYRIErUIUH57lvHDh+EgVwB7lRdSW8gAdtPNTC7o1VovgOuxaeuS8rITQURsDeGnX+m+s2WENV5Y8//xAS/trZH+fD746RXhgDyqIQ6nlKK3jRzvFxrcEa9xWuk9CFsZCXXB+QzRsGVzy3YTIWkcGkkzToCRwsR1HOyYPusErpQHqhEHWUuqArY2E6AFqzExadUNKXNxAnnAugESPZAHq+maObHrHQPlG8cFvA0WjhVm/rrhdRkgRdL1JCaJrQuO9F1DtHWBR/XIxWuHM8x8Z5qcFLdW9Cff8CRuIPyjBwYHjGly0agpb8N8kUhcFw53JF4LNMq4TuymLlXubcS6OhtuYkCxRVoyH6cQ1loziGLJxpbC46pXWHQGO9c/XTNejVsIVjj4zLucVkK3zofOpTpQuPSxj9RDkntAtVStk2Ggw7mvIy5V/pZshCOiYgtcGQG3sDcSBcmmu2ze7UN7ItwoXBCNdUkuOSkFVxK5i7BP5DfF/rGveQ1Ad46A/9AE1oP7fjJlQzK457VF17+BbABuoSF8DwaRGSzPnxfl5yXlWAy7MbEBwD3CxvfwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Order_ID = _t, Item_ID = _t, Notes = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order_ID", Int64.Type}, {"Item_ID", Int64.Type}, {"Notes", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Notes", Splitter.SplitTextByDelimiter("<BR/>", QuoteStyle.Csv), {"Notes.1", "Notes.2", "Notes.3", "Notes.4", "Notes.5", "Notes.6", "Notes.7", "Notes.8", "Notes.9", "Notes.10", "Notes.11"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Notes.1", type text}, {"Notes.2", type text}, {"Notes.3", type text}, {"Notes.4", type text}, {"Notes.5", type text}, {"Notes.6", type text}, {"Notes.7", type text}, {"Notes.8", type text}, {"Notes.9", type text}, {"Notes.10", type text}, {"Notes.11", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Order_ID", "Item_ID"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns", "Value", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Value.1", "Value.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Value.1", type date}, {"Value.2", type text}}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type2", "Value.2", Splitter.SplitTextByEachDelimiter({"MDT"}, QuoteStyle.Csv, false), {"Value.2.1", "Value.2.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Value.2.1", type time}, {"Value.2.2", type text}})
in
    #"Changed Type3"