Replace single text value with multiple text values

[ianmonat]

Hello, I track my organization's sales activity and I've got an issue where we had some personnel changes to the sales team. 

Referencing the screenshot below, if you see the 3rd column 'DVP', there is a value called 'East DVP'.

That position was recently split into two positions, so now there is a NorthEast DVP (New Value 1), and a SouthEast DVP (New Value 2).

When I use Power BI's Replace Values feature, I can only replace a single value with another single value, but I need two new values. I don't want to create another column or table if I don't have to. If there is a DAX-based solution, the condition can be based on the values in the 2nd column, 'Regional Mgr', which are concealed for privacy.

Thanks in advance for your help!

 screenshot of salesperson lookup table

[ianmonat · Accepted solution]

Thank you for your suggestions, I found a much easier solution that required no DAX, I inserted a Conditional Column and based the condition from the Regional Mgr column. 

[Anonymous]

Hi 

 First, use the replace transform and replace your value as like below.

  East DVP -> NorthEast DVP,  SouthEast DVP

 Second, use the below script and you need to modify the table and column names.

NewTable = SUMMARIZE(
        ADDCOLUMNS(
            FILTER(
                CROSSJOIN(
                     SUMMARIZE( 
                            Sub,
                            Sub[Name],
                            Sub[Subject],
                            "SubCount",1+LEN(Sub[Subject])-LEN(SUBSTITUTE(Sub[Subject],",","")))
                ,dummy)
            ,dummy[dummy]<=[SubCount])
        ,"Subject Name",PATHITEM(SUBSTITUTE(Sub[Subject],",","|"),dummy[dummy]))
, Sub[Name]
,[Subject Name])

in the above script, "Sub" and "dummy" are tables. Let me know if you need any other help.

Thanks

Hari

[Anonymous]

Hi

  Check the below link, it will help you for your requirement.

http://sqljason.com/2013/06/split-delimited-row-into-multiple-rows.html

Thanks

Hari