extract specific text strings to new column (without using delimiters)

[bingbong]

Hi 

I've just joined the community and hoping to get some help with the following. 

I have a single column in my dataset which contains two categories of data (all text)

Category 1 consists of three team codes appearing as:

mri; rp; ipc

Category 2 consists of three kpi's appearing as:

strategic advice; grow impact; service improvement

The data is separated by delimiters but occurs in different combinations/sequences.

(See below in the 'current' column)

Obviously all of this in one column is no good (and unfortunately i'm not able to fix this at the source).

So I need to 'move' the KPI's into a new 'KPI's' column, and remove them from what is intended to be the 'Teams' column (see 'desired' columns).

I also need to allow for the possibility of multiple kpi's being applied to a single row within the new KPI column. 

I have been looking at using 'Text.Select'  but i'm kind of a novice and a bit unsure about the syntax i need plus whether this would be the most effeicient solution.

Any help or advice would be greatly appreciated.

Thanks.

[bingbong]

Hi wdx223_Daniel!

Thanks - it worked!  

I'm just left with errors where the 'nulls' were.

Any suggestion for best way to deal with that? 

[bingbong]

Ah - back to previous step - replace null with white space

[wdx223_Daniel · Accepted solution]

NewStep=Table.SplitColumn(PreviousStepName,"Labels",each let a=Text.Split(_,";") in {Text.Combine(List.Intersect({a,{"MRI","RP","IPC"}}),";"),Text.Combine(List.Intersect({a,{"Strategic Advice","Grow Impact","Service Improvement"}}),";")},{"Teams","KPIs"})