Forum Discussion

igonzalezb's avatar
igonzalezb
Icon for Helper I rankHelper I
5 years ago
Solved

Split from digit to non digit only once

I would like to split a text column by character transition (digit to non digit), but only once from left to right.  SplitTextByCharacterTransition doesn't seem to have that option. Also, I don't k...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  igonzalezb ,

     

    Use below M codes:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsrAwNNI1STQ0Mk40NExUitWJVkpMijc2yTM1g3KSzcxMSoxNlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Text", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let 
l = Text.ToList([Text]),
p = List.PositionOfAny(l,{"0".."9"}),
txt = 
if Value.Is(l{0},type text)
then List.RemoveFirstN(l,p)
else l,
p1 = List.PositionOfAny(txt,{"0".."9"}),
p2 = List.PositionOfAny(txt,{"A".."z"}&{"-"}),
res = Text.Combine( List.Range(txt,p1,p2-p1),"")
in res),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each let 
l = Text.Split([Text],[Custom]),
res = l{0}&[Custom]
in 
res
),
    Custom1 = Table.AddColumn(#"Added Custom1", "Custom.2", each let 
l = Text.Split([Text],[Custom]),
res = l{1}
in
res
)
in
    Custom1

    And you will see:

    For the related .pbxi file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

Anonymous's avatar
Anonymous
Not applicable
5 years ago

questa funziona se il tuo testo non comincia con caratteri alfabetici.

 

 Table.SplitColumn(Origine, "Colonna1", each {Text.Start(_,Text.PositionOfAny(_,{"a".."z"})),Text.End(_,Text.Length(_)-Text.PositionOfAny(_,{"a".."z"}))})
igonzalezb's avatar
igonzalezb
Icon for Helper I rankHelper I
5 years ago

Thanks! My text may or may not start with a non digit. Any ideas?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago
    let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JY07EsQwCEPv4jqNgOwexkktRvj+9WJvwU8jPeYcBtj9/RRV6nKYitl7GMRilKyEsEU8T4vjveaAudfKWh6wVR4ZeW7kqjYfU4pugpJUetM2PLm5zenpDOgPPLo5dwSEjIJ38/3w/QE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [tetx = _t]),
    #"Dividi colonna per passaggio carattere" = Table.SplitColumn(Origine, "tetx", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c))),
    #"Merge di colonne" = Table.CombineColumns(#"Dividi colonna per passaggio carattere",{"tetx.2", "tetx.3", "tetx.4", "tetx.5", "tetx.6", "tetx.7", "tetx.8"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Sottoposto a merge")
in
    #"Merge di colonne"