Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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.
Solved! Go to 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"})
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
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"})