Forum Discussion

Tomhayw's avatar
Tomhayw
Icon for Helper I rankHelper I
3 years ago
Solved

Split column by delimiter with a character immediately after it

Hi there,

 

I have this (dummy) string of text that I am trying to split into columns:

 

This,piece, of,text,needs,to,be,split

 

The delimiter I want to split by has a character immediately before and after it.

Thispiece, oftextneedstobesplit

 The one that I don't want to split as a delimiter has a space immediately after it.

 

How do I go about this?

 

Thanks,

Tom

  • Anonymous's avatar
    Anonymous
    3 years ago

    You can use Replace values, and replace ", " with just " " or something that sticks out like "*', and then split by the comma ",".

     

    --Nate

3 Replies

  • A fancier way to do this would be to use a character transition splitter. This will keep the commas though, so you'd probably want to trim those off.

     

    Full sample query you can paste into the Advanced Editor of a new blank query:

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsnILNYpyExNTtVRyE/TKUmtKNHJS01NKdYpyddJStUpLsjJLFGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]), 
      #"Split Column by Character Transition" = Table.SplitColumn(
        Source, 
        "Text", 
        Splitter.SplitTextByCharacterTransition({","}, each _ <> " ")
      ), 
      #"Trimmed Text" = Table.TransformColumns(
        #"Split Column by Character Transition", 
        {}, 
        each Text.TrimEnd(_, ",")
      )
    in
      #"Trimmed Text"

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable

    You can use Replace values, and replace ", " with just " " or something that sticks out like "*', and then split by the comma ",".

     

    --Nate

    • Tomhayw's avatar
      Tomhayw
      Icon for Helper I rankHelper I

      Thank you,

       

      I was too busy trying to overcomplicate it I forgot about the easy way around!