Problem splitting column with delimiter

Question

Hi,&nbsp;I have split the column "Fldvalue" based on the delimiter " | ".&nbsp;See table: The third and fourth column of the sample have been split.&nbsp;&nbsp;IDFldnameFldvalue.1Fldvalue.21267A_employeeTeam_Option|SalesPeter1267B_managerString_Option|CommerceManager Dudley1267C_subjectDescription A&nbsp;7654A_employeeTeam_Option|PurchaseJohn3489A_employeeTeam_Option|SalesPeter3489B_managerString_Option|MarketingManager Michele3489C_subjectDescription B&nbsp;2367A_employeeTeam_Option|PurchaseMandy2367B_managerString_Option|MarketingManager Michele2367C_subjectDescription C&nbsp;6812A_employeeTeam_Option|PurchaseJohn6812B_managerString_Option|CommerceManager Blake6812C_subjectDescription D&nbsp;&nbsp;Unfortunately, not alle values in the original column have the "|" delimiter. The result is that some values in column Fldvalue.2 are empty. If you look more closely, it concerns the values of "C-subject" in Column Fldname.&nbsp;I want column Fldvalue.2 to show all values, including the values of "C-subject".&nbsp;Is it possible with query editor/DAX to move the values for variable "C_ subject" from column Fldvalue.1 to Fldvalue.2?&nbsp;Thanks in advance.Mike&nbsp;

jimmy801 · Accepted Answer

Hello Mike_T&nbsp;
&nbsp;
or you change your dataset and place there the limiter two times or you apply two time Table.ReplaceValue. Here an example
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZI/D4IwEMW/CunMIirgyJ/JhEiCGyGk1ougbSFtGUj88BZE0gUS3Hov97u+d7k8RzvH9ZCNghJYS5seQBdXwKy8tKpu+DvDFKTWUlAgUGHPRFgyzPFDizbKlKj544dEDWMgyDAp+bZYcXen0Jt4VMru9gSi9DsGSUQ9wlaga2ts9NzjYc1Z2glSYTnI56biI7M/+KdtaSZiOU2CxQuUFow4SU0qoGAOWMoTznmc/fqmjTz6n3tvQv/bmwYs2Ytme66/c7aue2I2nUJI8QtMesla/LVWfAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Fldname = _t, Fldvalue.1 = _t, Fldvalue.2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Fldname", type text}, {"Fldvalue.1", type text}, {"Fldvalue.2", type text}}),

Replace = Table.ReplaceValue
    (
        #"Changed Type",
        each [#"Fldvalue.2"], 
        (row)=&gt; if row[Fldname]="C_subject" then row[#"Fldvalue.1"] else row[#"Fldvalue.2"] ,
        Replacer.ReplaceValue,
        { "Fldvalue.2"}
    ),
    Replace2 = Table.ReplaceValue
    (
        Replace,
        each [#"Fldvalue.1"], 
        (row)=&gt; if row[Fldname]="C_subject" then "" else row[#"Fldvalue.1"] ,
        Replacer.ReplaceValue,
        { "Fldvalue.1"}
    )
in
    Replace2
&nbsp;
Copy paste this code to the advanced editor in a new blank query to see how the solution works.If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)Kudoes are nice tooHave funJimmy
&nbsp;
&nbsp;

Forum Discussion

Problem splitting column with delimiter

2 Replies

Related content

split column by delimiter in dax

DAX formula help splitting string using delimiters

Splitting multiple columns using delimiter

split by delimiter

How to split strings by a delimiter?

ID	Fldname	Fldvalue.1	Fldvalue.2
1267	A_employee	Team_Option\|Sales	Peter
1267	B_manager	String_Option\|Commerce	Manager Dudley
1267	C_subject	Description A
7654	A_employee	Team_Option\|Purchase	John
3489	A_employee	Team_Option\|Sales	Peter
3489	B_manager	String_Option\|Marketing	Manager Michele
3489	C_subject	Description B
2367	A_employee	Team_Option\|Purchase	Mandy
2367	B_manager	String_Option\|Marketing	Manager Michele
2367	C_subject	Description C
6812	A_employee	Team_Option\|Purchase	John
6812	B_manager	String_Option\|Commerce	Manager Blake
6812	C_subject	Description D