Forum Discussion

IvanS's avatar
IvanS
Helper V
3 years ago
Solved

Extract text after specific text string

Hi guys,   I am trying to get location code from the column Subject in table FACT_Tasks - example below:   Logic: If Subject contains (anywhere) the specific text string (AAAA or BBBB), then - I...
Need Help
Tips and Tricks
  • Ahmedx's avatar
    Ahmedx
    3 years ago

    you can change the numbers 10 and 12 to any other numbers if it is necessary for you

ThxAlot's avatar
ThxAlot
Super User
3 years ago
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkmtKCmBYqVYHaBAYnG2gqGCroIjEBgaGZuYmiHEg0uTslKTSxT8EnNTgUqcgACixFzXwlIpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Subject = _t]),

    Extracted = Table.AddColumn(Source, "Location code", each if Text.Contains([Subject], "AAAA") then Text.Range([Subject], Text.PositionOf([Subject], "AAAA"), 10) else try Text.Range([Subject], Text.PositionOf([Subject], "BBBB"), 12) otherwise null)
in
    Extracted