Split column by text

[giulio23]

Hi all,

Could you please help me with the following scenario?

I have data imported from our ERP with columns for invoice nr, product id and product name. The problem is that the order nr appears in the ProductName column and I have nulls every time this happens. I would like to split the ProductName into 2 new columns: one for Order Nr and one for Order Date and then fill down for each invoice. 

Here's a snapshot of how my data looks like:

InvoiceNr.	ProductID	ProductName
1		Order 25 of 01.01.2019
1	A1	Product A
1	B1	Product B
1	C1	Product C
2		Order 30 of 02.01.2019
2	A1	Product A
2	B1	Product B
3	C1	Product C
3	D1	Product D
3	E1	Product E
4		Order 31 of 02.01.2019
4	C1	Product C

[mahoneypat · Accepted solution]

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

[AlB · Accepted solution]

@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 

[ziying35 · Accepted solution]

@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

[AlB · Accepted solution]

@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 

[mahoneypat · Accepted solution]

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]

Thank you very much, Pat. It worked like a charm 🙂 

Happy Holidays

[giulio23]

Hi @AlB,

Thank you for your reply. 

Here's how I would like the end result to be:

InvoiceNr.	ProductID	ProductName	Order	Order Date
1	A1	Product A	25	01.01.2019
1	B1	Product B	25	01.01.2019
1	C1	Product C	25	01.01.2019
2	A1	Product A	30	02.02.2019
2	B1	Product B	30	02.02.2019
3	C1	Product C	30	02.02.2019
3	D1	Product D	30	02.02.2019
3	E1	Product E	30	02.02.2019
4	C1	Product C	31	02.01.2019

Best wishes

[AlB]

Hi @giulio23 

Ok, so what exactly is the expected result for the sample above? Can you show the expected output table, so that the requirement is completely unambiguous?

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