Split column with no clear delimiter

[Anonymous]

I need to split a column of data in two but there isn't any obvious delimiter nor uniform number of characters. Below in text and also linked is a sample of my data my split requirements.

1/1-4 Novus to 1/1-4 and Novus

3/5-14 S Hipe to 3/5-14 S and Hipe

16/1-11, 11S Ripe Teel to 16/1-11, 11S and Ripe Teel

25/5-7 David (Alto) to 25/5-7 and David (Alto)

16/3-4, -4A Avad (JS) to 16/3-4, -4A and Avad (JS)

6607/1-02 Jappe/Test to 6607/1-02 and Jappe/Test

I suspect this is not possible but would like confirmation before I request we change our inputs at the data entry level. 

https://docs.google.com/spreadsheets/d/13qM7sbDV5zlUF238F5cjUQPKvVJYRflBQoZOfyp8o-Y/edit#gid=0

Thanks

[v-frfei-msft]

Hi @Anonymous ,

We can do part of steps in power query like this.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NYyxCoMwFEV/5ZKphYT4NMY50KE4ODRuIUOgbxAEA9p8f1Ol6znn3hAEaVIG01Y+u4gyiE73igw8nktmnIhsbYgkiDxePzwzr6dq+5oPeKSyvHFz67Hd/5NOGQllHFxJ1Y3+MtY2Q71rWowpZ9Yz74eI8Qs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Remove([Column1],{"a".."z","A".."Z","(",")"})),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Custom.1", "Custom.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Custom.2"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Custom", each Text.Length([Custom.1])+1),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", Int64.Type}})
in
    #"Changed Type2"

Then we can create two calculated column based on it.

left = LEFT('Table'[Column1],'Table'[Custom])

right = RIGHT('Table'[Column1],LEN('Table'[Column1])-'Table'[Custom])

[Anonymous]

@v-frfei-msft  Many thanks for the help.

I can manage the first part using the suggested M Code but I'm not too familiar with custom columns and I'm getting a Token Literal Expected error. When I click on Show Error it highlights the first single quotation mark on 'Table'[Column1].

What exactly does Token Literal mean in this instance?

[kcantor]

@Anonymous 

The second part appears to be building two calculated columns in PowerBi using Dax, not in the Query Editor using M. Load your query and attempt to make those columns in Dax.

[Anonymous]

@kcantor  Thanks, you're right. The second part is done with a calculated column using DAX and unfortunately I have to make this work in Excel.

Can this be done either with calculated columns in Excel's Power Query or by modifying the M Code?

Thanks

[kcantor]

@Anonymous 

I am going to take a blind stab at this.

In PowerQuery / Query Editor, Left and Right function differently. You already have the calculated number of characters. You would need to wrap that in the correct syntax:

Excel: = LEFT (text, num_chars) would be like =LEFT("Excel", 2) would result in EX

Query = Text.Start(text, num_chars) would be like =Text.Start("Excel", 2) and would result in Ex to match.

For right you would need to change Text.Start to Text.End

This could be used in the calculated column while referencing other columns.

[kcantor]

@Anonymous 

I have come in on the late side of this and was just explaing the erroryou were receiving. I do not have access to the sample data you provided.

I am not sure what you mean that you need to do this in Excel. Do you mean Query Editor? PowerBI? PowerPivot?  

Also, if you can grant access to the file, I would be happy to take a look but please be specific about your desired output software.

[Anonymous]

@kcantor  Of course, I appreciate that. 

I'm still not sure of the best way to share my sample data on here but I did provide this link in my original post - apologies if there is a better way, if so I'll edit my post accordingly. 

I need to do this in Excel's Query Editor and need the input on the left to be split into two columns, as below.

1/1-4 Novus to 1/1-4 and Novus

3/5-14 S Hipe to 3/5-14 S and Hipe

16/1-11, 11S Ripe Teel to 16/1-11, 11S and Ripe Teel

25/5-7 David (Alto) to 25/5-7 and David (Alto)

16/3-4, -4A Avad (JS) to 16/3-4, -4A and Avad (JS)

6607/1-02 Jappe/Test to 6607/1-02 and Jappe/Test

Thanks in advance.

[kcantor]

@Anonymous 

When I click the link for your sample data it says I do not have permission to access the document. It allows me to request access, which I do, but it says I will be emailed if/when access is granted. If you could allow me access to the sample data, perhaps I could offer more insight. 

[Anonymous]

Apologies for that, the link below should share the small sample I provided in my text.

https://docs.google.com/spreadsheets/d/13qM7sbDV5zlUF238F5cjUQPKvVJYRflBQoZOfyp8o-Y/edit?usp=sharing

[kcantor]

@Anonymous 

Give this a try:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NYyxCoMwFEV/5ZKphYT4NMY50KE4ODRuIUOgbxAEA9p8f1Ol6znn3hAEaVIG01Y+u4gyiE73igw8nktmnIhsbYgkiDxePzwzr6dq+5oPeKSyvHFz67Hd/5NOGQllHFxJ1Y3+MtY2Q71rWowpZ9Yz74eI8Qs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Remove([Column1],{"a".."z","A".."Z","(",")"})),
#"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Custom.1", "Custom.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Custom.2"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns", "Custom", each Text.Length([Custom.1])+1),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", Int64.Type}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type2", "Left", each Text.Start([Column1], [Custom])),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Right", each Text.End([Column1], Text.Length([Column1])-[Custom]))
in
#"Added Custom3"

[Anonymous]

@kcantor So close. 

I just realised that the D in the Left column when it should be the first letter of the Right column i.e. 25/5-7 and David (Alto).

[kcantor · Accepted solution]

@Anonymous 

How about this, then?

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NYyxCoMwFEV/5ZKphYT4NMY50KE4ODRuIUOgbxAEA9p8f1Ol6znn3hAEaVIG01Y+u4gyiE73igw8nktmnIhsbYgkiDxePzwzr6dq+5oPeKSyvHFz67Hd/5NOGQllHFxJ1Y3+MtY2Q71rWowpZ9Yz74eI8Qs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Remove([Column1],{"a".."z","A".."Z","(",")"})),
#"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Custom.1", "Custom.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Custom.2"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns", "Custom", each Text.Length([Custom.1])),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", Int64.Type}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type2", "Left", each Text.Start([Column1], [Custom])),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Right", each Text.End([Column1], Text.Length([Column1])-[Custom]))
in
#"Added Custom3"

[Anonymous]

@kcantor Excellent - thanks so much for your help.

[kcantor]

Happy to help.