Begginer question : Delimiter question

[Anonymous]

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.

[Anonymous]

Thank you everyone, I was able to do it thanks to all your help !!!

[AlexisOlson · Accepted solution]

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"

[Anonymous]

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

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

[serpiva64]

Hi,

please specify which is the result you need to achieve from the exemple