Forum Discussion
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 image
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]
3 Replies
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]Is there a way to extract a substring where I do not know the length? I know the pattern of the start but I do not know when the string I am expecting terminates. If I have to run a python script, how would I do it?
