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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Shelley ,

Please try this:

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

And the final output is as below:


Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.