cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mattsasso Helper I
Helper I

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

Accepted Solutions
kcantor Community Champion
Community Champion

Re: Split column with no clear delimiter

@mattsasso 

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 Datanaut!




View solution in original post

14 REPLIES 14
Community Support
Community Support

Re: Split column with no clear delimiter

Hi @mattsasso ,

 

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.
mattsasso Helper I
Helper I

Re: Split column with no clear delimiter

@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

Re: Split column with no clear delimiter

@mattsasso 

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 Datanaut!




mattsasso Helper I
Helper I

Re: Split column with no clear delimiter

@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

Re: Split column with no clear delimiter

@mattsasso 

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 Datanaut!




kcantor Community Champion
Community Champion

Re: Split column with no clear delimiter

@mattsasso 

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 Datanaut!




mattsasso Helper I
Helper I

Re: Split column with no clear delimiter

@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

Re: Split column with no clear delimiter

@mattsasso 

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 Datanaut!




mattsasso Helper I
Helper I

Re: Split column with no clear delimiter

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

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors