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

- If AAAA - then take 10 characters (including AAAA)

- If BBBB - then take 12 characters (including BBBB)

 

I would prefer to have this in DAX as calculated column and not via Power Query.

 

SubjectLocation code (calculated)
Texttexttext(blank)
Task 1 - AAAA123456AAAA12345
Task Subject Name - BBBB1234567-89BBBB12345-67

 

Thank you
IvanS

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

6 Replies

  • Ahmedx's avatar
    Ahmedx
    Super User
    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

  • Ksyokz23's avatar
    Ksyokz23
    Frequent Visitor
    2 years ago

    How about if I want to do same but this time with Power Query not with DAX