Forum Discussion
extract specific text strings to new column (without using delimiters)
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.
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"})
3 Replies
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"})
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?
Ah - back to previous step - replace null with white space
