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 same substring can appear in some of the actual Job Titles, so IF the substring " - " is there, I want to remove everything before only the first instance. 

 

Here's some data just for example:

Job Title

Material Handler Level 1P
Material Handler Level 2P
Material Handler Level 3P
Material Handler Level 4P
OM.015.S3 - Equip Maintenance Technician Level 3P
OM.015.S4 - Equip Maintenance Technician Level 4P
OM.015.S5 - Equip Maintenance Technician Level 5P

 

I've tried this, but am getting an error.

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


Here's the error I receive:

Expression.Error: We cannot apply field access to the type Text.

 

When I tried it without the condition, it erased the field, which I'm thinking happened because those first fields in the data did not contain the " - " string.

Thanks in advance for the help.

 

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

5 Replies

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

    • Shelley's avatar
      Shelley
      Post Prodigy
      2 years ago

      Thanks Anonymous and OwenAuger - and Owen for explaining it. Unfortunately, I still don't understand single argument and inner list terminology, but I love having the explanation. One of these days I'll understand M better. In the meantime, this worked! THANK YOU both.

  • 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.

  • 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.