Forum Discussion
Split column with no clear delimiter
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"
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.
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
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"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
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"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 Excellent - thanks so much for your help.
Happy to help.