Forum Discussion

Shelley's avatar
Shelley
Post Prodigy
2 years ago
Solved

Conditionally Remove Substring from Beginning of Column

Hi Team,  Another plea for help here. I'm trying to clean up some messy Job Title data. Sometimes there is a job code at the beginning of the Job title that is delineated by " - "; however, this sam...
Need Help
  • OwenAuger's avatar
    OwenAuger
    2 years ago

    Hi Shelley 

     

    Change [Job Title] to _

    #"Trimmed Text1" = Table.TransformColumns(#"Trimmed Text", {{"Job Title", each if Text.Contains(_, " - ") then Text.AfterDelimiter(_, " - ") else _ , type text}})

    For the 2nd argument of Table.TransformColumns, the second item within each inner list needs to be a function taking a single argument, which is represented by _ when using the each syntax.

     

    Regards

poojashribanger's avatar
poojashribanger
Helper I
2 years ago

Hi,

 

Under Transform Tab there is option Extract Text after delimiter. Try that it works.

 

Regards,

Poojashri

  • Shelley's avatar
    Shelley
    Post Prodigy
    2 years ago

    poojashribanger Great idea, thanks. However, this gave me blanks just like when I tried to do it manually.