Forum Discussion
Helper ISplit from digit to non digit only once
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 Custom1And you will see:
For the related .pbxi file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
Helper IThank you. That's not really what I'm asking. I need to do a split by character transition from digit to non digit once.
If the text is 1abc123, the result should be 1 and abc123.
If the text is a16-abc123-asd34, the result should be a16 and -abc123-asd34
As I stated in my post, SplitTextByCharacterTransition does not have that option. It splits every instance of the transition, for example, a16-abc123-asd34 will return three columns a16, -abc123, -asd34. I don't know in advanced how many transitions could there be in my text.
Community SupportHi 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
Custom1And 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!