Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 know in advance how many transitions there are in any given row of the column.
Solved! Go to Solution.
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!
Hi @igonzalezb ,
If you wanna split a column to two columns,one is digit,the other is non digit,you need to create 2 columns for the split.Using below M codes:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkwqNjQyVorViVYC0okpZUqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [column = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"column", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Letter", each Text.Remove([column],{"0".."9"})),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Number", each Text.Split([column],[Letter])),
#"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Number", each Text.Combine(List.Transform(_, Text.From)), type text})
in
#"Extracted Values"
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Thank 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.
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!
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"}))})
Thanks! My text may or may not start with a non digit. Any ideas?
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.