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