cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
giulio23
Frequent Visitor

Split column by text

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.ProductIDProductName
1 Order 25 of 01.01.2019
1A1Product A
1B1Product B
1C1Product C
2 Order 30 of 02.01.2019
2A1Product A
2B1Product B
3C1Product C
3D1Product D
3E1Product E
4 Order 31 of 02.01.2019
4C1Product C
3 ACCEPTED SOLUTIONS
mahoneypat
Super User IV
Super User IV

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

AlB
Super User III
Super User III

@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 

SU18_powerbi_badge

 

View solution in original post

ziying35
Impactful Individual
Impactful Individual

@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

 

View solution in original post

6 REPLIES 6
ziying35
Impactful Individual
Impactful Individual

@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

 

View solution in original post

AlB
Super User III
Super User III

@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 

SU18_powerbi_badge

 

View solution in original post

mahoneypat
Super User IV
Super User IV

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

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

Happy Holidays

giulio23
Frequent Visitor

Hi @AlB,

 

Thank you for your reply. 

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

InvoiceNr.ProductIDProductNameOrderOrder Date
1

A1

Product A2501.01.2019
1B1Product B2501.01.2019
1C1Product C2501.01.2019
2A1Product A3002.02.2019
2B1Product B3002.02.2019
3C1Product C3002.02.2019
3D1Product D3002.02.2019
3E1Product E3002.02.2019
4C1Product C3102.01.2019


Best wishes

AlB
Super User III
Super User III

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 

 

SU18_powerbi_badge

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors