Forum Discussion
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
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"
14 Replies
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])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?
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.
