cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Split column with no clear delimiter

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

1 ACCEPTED SOLUTION
kcantor
Community Champion
Community Champion

@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"





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

14 REPLIES 14
v-frfei-msft
Community Support
Community Support

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])

Capture.PNG

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

@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
Community Champion
Community Champion

@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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

@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
Community Champion
Community Champion

@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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




kcantor
Community Champion
Community Champion

@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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

@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
Community Champion
Community Champion

@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. 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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
Community Champion
Community Champion

@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"





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

@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
Community Champion
Community Champion

@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"





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Anonymous
Not applicable

@kcantor Excellent - thanks so much for your help.

kcantor
Community Champion
Community Champion

Happy to help.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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 Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors