Forum Discussion
Begginer question : Delimiter question
Hello, I'm a begginer with Power Query/BI and I have a question.
I have this data :
Z180711^E-5883]20^7.79Z10.52]210.4\155.8[54.6[
Z585386\K-5241^15]18.5Z19.98\299.7\277.5]22.2]
D386060]B-5318[23[6.21\6.64Z152.72[142.83]9.88999999999999Z
E394618\B-5318]23_9.48]10.9Z250.7[218.04Z32.66]
Thanks to someone from this forum I was able to learn how to delimiter with different delimiters but my problem is: I have Z as a delimiter (You can see in the middle of the data) but Z is also part of the transaction ID sometimes (which are at the start). Of course I want to use Z as a delimiter but I don't want the delimiter to work on the ID transaction.
I thought about lowercase one of them but I couldn't make it work sadly.
Do you have an idea on how I could do please?
Thank you so much !
Henri D.
If it's always the letter in the first position you want to ignore, then you can remove it, split by delimiters, then put it back.
Here's an example query you can paste into the Advanced Editor of a new blank query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY4xcgMxDAP/4tqHESGRIttMXOUHknj+/y/CcZqgQbXA7v1Y4m2K3K9L3Xuy3RMzljQok1XjHFGFbx2w/chnMera3c75uZRDbtEUhy4JhJ/DCMyqOaFJgvmhvgtp1vLr0i6+2beBco7BxhIlJrcMoiwC7vEv6zPw6jFM6uBvIdnfgeFZkrGoDXOzPNpYnTCr1/wF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Split Text by Delimters" = Table.TransformColumns(Source,{{"Column1", each Text.Trim(Text.Start(_, 1) & Text.Combine(Text.SplitAny(Text.RemoveRange(_, 0), "Z^/\[]"), " ")), type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Split Text by Delimters", "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8"}) in #"Split Column by Delimiter"
5 Replies
- AlexisOlson
Super User
If it's always the letter in the first position you want to ignore, then you can remove it, split by delimiters, then put it back.
Here's an example query you can paste into the Advanced Editor of a new blank query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY4xcgMxDAP/4tqHESGRIttMXOUHknj+/y/CcZqgQbXA7v1Y4m2K3K9L3Xuy3RMzljQok1XjHFGFbx2w/chnMera3c75uZRDbtEUhy4JhJ/DCMyqOaFJgvmhvgtp1vLr0i6+2beBco7BxhIlJrcMoiwC7vEv6zPw6jFM6uBvIdnfgeFZkrGoDXOzPNpYnTCr1/wF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Split Text by Delimters" = Table.TransformColumns(Source,{{"Column1", each Text.Trim(Text.Start(_, 1) & Text.Combine(Text.SplitAny(Text.RemoveRange(_, 0), "Z^/\[]"), " ")), type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Split Text by Delimters", "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8"}) in #"Split Column by Delimiter" - serpiva64
Solution Sage
Hi,
please specify which is the result you need to achieve from the exemple
I would love to get this result :
Z180711^E-5883]20^7.79Z10.52]210.4\155.8[54.6[ --> Z180711 E-5883 20 7.79 10.52 210.4 155.8 54.6
Z585386\K-5241^15]18.5Z19.98\299.7\277.5]22.2] --> Z585386 K-5241 15 18.5 19.98 299.7 277.5 22.2
D386060]B-5318[23[6.21\6.64Z152.72[142.83]9.88999999999999Z --> D386060 B-5318 23 6.21 6.64 152.72 142.83 9.88999999999999
E394618\B-5318]23_9.48]10.9Z250.7[218.04Z32.66] --> E394618 B-5318 23 9.48 10.9 250.7 218.04 32.66As you can see I have several delimiters like [ ] / \ but also Z. But when I add Z into the list of my delimiters it separates also the Z which is the first letter and is suppose to stay stick with the first numbers.
Sorry if it wasn't clear.
- serpiva64
Hi,
can't you try to first split the first column then you can procede as you know
If this post is useful to help you to solve your issue consider giving the post a thumbs up 👍 and accepting it as a solution !
