Forum Discussion

adavid999's avatar
adavid999
Icon for Helper V rankHelper V
5 years ago
Solved

Split column where ",*"

hello, does anyone know how I could split column where I have string ",t" (that is a comma and no space before next character).   Here the "t" is an example, the letters vary.   Example:   "spo...
Need Help
  • AlB's avatar
    AlB
    5 years ago

    Hi adavid999 

    Place the following M code in a blank query to see the steps:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKi7ILyopVsjMS84pTUktVkjLzy9JSszJ0VEoSc3LyyzWSUpMyc3MK8nPU4rVIUJ5RmJeCkhAKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}}),

    #"Split Column by Character Transition" = Table.SplitColumn(#"Changed Type", "Col1", Splitter.SplitTextByCharacterTransition({","}, (c) => List.Contains({"a".."z", "A".."Z" }, c)), {"Col1.1", "Col1.2"})
in
    #"Split Column by Character Transition"

     

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

adavid999's avatar
adavid999
Icon for Helper V rankHelper V
5 years ago

thank @aib, I see that this works in your example but I am struggling to get it to work on my data. Specifically I am unsure how to apply this to my existing variables. I have no idea about M code.

 

Is it a case of writing the following in the formula bar and changing the "Col1" names?

 

= Table.SplitColumn(#"Changed Type", "Col1", Splitter.SplitTextByCharacterTransition({","}, (c) => List.Contains({"a".."z", "A".."Z" }, c)), {"Col1.1", "Col1.2"})

Best,

Adam