Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Shelley
Continued Contributor
Continued Contributor

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.

 

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

5 REPLIES 5
v-junyant-msft
Community Support
Community Support

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:

vjunyantmsft_0-1708668904338.png


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
Frequent Visitor

Hi,

 

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

poojashribanger_0-1708664986638.png

 

Regards,

Poojashri

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

OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Shelley
Continued Contributor
Continued Contributor

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors