Forum Discussion
Split/extract text after different delimiter
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!
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
3 Replies
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
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?
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
