Conditionally Remove Substring from Beginning of Column

[Shelley]

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.

[v-junyant-msft]

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]

Hi,

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

Regards,

Poojashri

[Shelley]

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

[OwenAuger · Accepted solution]

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]

Thanks @v-junyant-msft 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.