Extracting String From Column into New Column

[Anonymous]

Hello, I am unsure of how to extract the values beginning with CRQ (like 'CRQXXXX' or CRQ00002382610) into a new column. 

Currently I have the column:

 

I would like to create a new column:

ID

CRQXXXX

CRQ000002382610

CRQXXXX

CRQ000002378563

This table/data source is being accessed using Direct Query so that may affect the solution.

[Anonymous]

you can try using the search function in combination with Find and Len

SEARCH(<find_text>, <within_text>[, [<start_num>][, <NotFoundValue>]])  

 

https://docs.microsoft.com/en-us/dax/search-function-dax

[nandic · Accepted solution]

Hi @Anonymous ,

Once published i have seen last row of your question where it is said that direct query is used. In this case, the best option is to make this column on database table level.

If Power Query (depending on database) allows extract options, below are steps:

Select the column (Issue Title) > Extract > Text between delimiters 
For delimiter enter space.

Cheers,
Nemanja

[Anonymous]

@nandic ,

I appreciate the prompt response. Unfortunately I get the error "this step results in a query that is not supported in DirectQuery mode" so I am unsure if it is successful. I will see if I can import the table and will retry your solution.

[nandic]

@Anonymous , in this case there are two options:
1) adding this column in database table
2) importing this table, as you suggested. In that case, steps from above will work.