Split/extract text after different delimiter

[tgjones43]

Hi all

I have data that looks like the first column below. I'd like to extract the < and > symbols to one column and the numbers to a different column. I think I know how to do this using several queries but is it possible to do with just one query?

My Data	Sign	Value
7		7
7.2		7.2
<7	<	7
<7.2	<	7.2
>7	>	7

Thank you!

[Anonymous · Accepted solution]

HI @tgjones43,

You can refer to the following code to add two custom columns in the query editor to achieve your requirement:

#"Added Custom" = Table.AddColumn(Source, "Sign", each if Value.FromText([My Data]) is text then Text.Start([My Data],1) else "")

#"Added Custom1" = Table.AddColumn(#"Added Custom", "Value", each if Value.FromText([My Data]) is text then Value.FromText( Text.RemoveRange([My Data],0,1)) else Value.FromText([My Data]))

Full query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMleK1QGSekZgOqbUwMA42RyZjSyTCpSJBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"My Data" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Sign", each if Value.FromText([My Data]) is text then Text.Start([My Data],1) else ""),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Value", each if Value.FromText([My Data]) is text then Value.FromText( Text.RemoveRange([My Data],0,1)) else Value.FromText([My Data])),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"Sign", type text}, {"Value", type number}})
in
    #"Changed Type"

Regards,
Xiaoxin Sheng

[amitchandak]

@tgjones43 , In power query you have the option split column by digits and nondigits

Split Column By Digit to Non Digit & Non Digit to Digit: https://youtu.be/tY4Yk1crS9s

[tgjones43]

@amitchandak thanks for the quick reply. I tried that but I can't get it to work. Because some of my values have a decimal point and some don't it doesn't work. Can you advise?