Forum Discussion
Extracting String From Column into New Column
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.
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
4 Replies
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,
Nemanjanandic ,
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.
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.
you can try using the search function in combination with Find and Len
SEARCH(<find_text>, <within_text>[, [<start_num>][, <NotFoundValue>]])