Split column by text

Here is one way to do it in the query editor.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUVIAYv+ilNQiBSNThfw0BQNDPSAyMjC0VIrVgShxBBEBRfkppcklCo5wYSdkYSe4sDOysDNY2AjFHmMDsD1GKPYYYbfHCLs9xtjtAQm7IAu7wIVdkYVdwcImqK4yxOIqEyz2xAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"InvoiceNr." = _t, ProductID = _t, ProductName = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"InvoiceNr.", Int64.Type}, {"ProductID", type text}, {"ProductName", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([ProductName], "Order") then [ProductName] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([ProductID] <> " ")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Custom", Splitter.SplitTextByEachDelimiter({" of "}, QuoteStyle.Csv, false), {"Custom.1", "Custom.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type date}}),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Changed Type1", {{"Custom.1", each Text.AfterDelimiter(_, "Order "), type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted Text After Delimiter",{{"Custom.1", "OrderNumber"}, {"Custom.2", "OrderDate"}})
in
    #"Renamed Columns"

Regards,

Pat

giulio23 

Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUVIAYv+ilNQiBSNThfw0BQNDPSAyMjC0VIrVgShxBBEBRfkppcklCo5wYSdkYSe4sDOysDNY2AjFHmMDsD1GKPYYYbfHCLs9xtjtAQm7IAu7wIVdkYVdwcImqK4yxOIqEyz2xAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"InvoiceNr." = _t, ProductID = _t, ProductName = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"InvoiceNr.", Int64.Type}, {"ProductID", type text}, {"ProductName", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "NameOrder", each if [ProductID] = " " then Text.Replace(Text.Start([ProductName], Text.PositionOf([ProductName], "of ", Occurrence.First)-1), "Order ", "") else null, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "OrderDate", each if [ProductID] = " " then Text.End([ProductName],10) else null, type date),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"NameOrder", "OrderDate"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"ProductName"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([ProductID] <> " "))
in
    #"Filtered Rows"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

giulio23 

try this:

let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65W8swry89MTvUr0lOyMtRRCijKTylNLvF0UbJSUlCC8/0Sc1OBIv5FKalFCkamCvlpCgaGekBkZGBoqVSrg9ccR0MMg6A8BUdCep1w63UipNcZt15nDL1GxPnd2ADsdyOcfjeiwO9GFPjdmAK/o+l1wa3XhZBeV9x6XTH0mhAZ5oYEwtyEFL/HAgA=",BinaryEncoding.Base64),Compression.Deflate))),

    rows = Table.ToRows(Source)&{{""," "}},
    acc = List.Accumulate(
              rows, 
              {"", {}, {}, {}},
              (s,c)=>if s{0}<>" " and c{1}?=" " 
                     then {c{1}?, List.Skip(Splitter.SplitTextByEachDelimiter({"Order ", " of "})(c{2}?)), {}, s{3}&{s{2}}} 
                     else {c{1}?, s{1}, s{2}&{c&s{1}}, s{3}}),
    result = Table.FromRows(List.Combine(acc{3}), Table.ColumnNames(Source)&{"Order", "Date"})
in
    result