cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Route11 Member
Member

Custom Column to extract STRING

Hi Experts

 

How would you extract all data from column Work Type that has in the string of text the letter ABC and or BCD anywhere in the string...into a new column and all other entries are blank in the new column.. so see example below....

 

see imageCapture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Super User II
Super User II

Re: Custom Column to extract STRING

try this PowerQuery code, apply it in Add Column>Custom Column window

[
abc = Text.PositionOf([Work Type],"ABC"),
bcd = Text.PositionOf([Work Type],"BCD"),
resultABC = if abc <> -1 then Text.Middle([Work Type],abc,3) else null,
resultBCD = if bcd <> -1 then Text.Middle([Work Type],bcd,3) else null,
result = Text.Combine({resultABC, resultBCD})
][result]
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

2 REPLIES 2
Super User II
Super User II

Re: Custom Column to extract STRING

try this PowerQuery code, apply it in Add Column>Custom Column window

[
abc = Text.PositionOf([Work Type],"ABC"),
bcd = Text.PositionOf([Work Type],"BCD"),
resultABC = if abc <> -1 then Text.Middle([Work Type],abc,3) else null,
resultBCD = if bcd <> -1 then Text.Middle([Work Type],bcd,3) else null,
result = Text.Combine({resultABC, resultBCD})
][result]
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

Route11 Member
Member

Re: Custom Column to extract STRING

Stachu...Excellent feedback....and happy to learn also....thank you

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors