Problem splitting column with delimiter

[Mike_T]

Hi,

I have split the column "Fldvalue" based on the delimiter " | ". See table: The third and fourth column of the sample have been split. 

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

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.

I want column Fldvalue.2 to show all values, including the values of "C-subject". Is it possible with query editor/DAX to move the values for variable "C_ subject" from column Fldvalue.1 to Fldvalue.2?

Thanks in advance.

Mike

[Jimmy801 · Accepted solution]

Hello @Mike_T 

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)=> 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)=> if row[Fldname]="C_subject" then "" else row[#"Fldvalue.1"] ,
        Replacer.ReplaceValue,
        { "Fldvalue.1"}
    )
in
    Replace2

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 too

Have fun

Jimmy

[v-xuding-msft · Accepted solution]

Hi @Mike_T ，

= Table.AddColumn(#"Changed Type", "Custom", each if [Fldvalue.2] = "" then [Fldvalue.1] else [Fldvalue.2])

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZI/C4MwEMW/imR2qVq1o3+mglSwm4ik6aGpiZYYB6EfvtFqyWLBbrnH/S7vHZfn6GC5HjJRUAJ/sm4EUMUVMC8vT0m79pVhBr3SUpAgUGF+ibDkuMWVEk2USUHbakWijnMQZJqUfFqMeLgzGHU8Kvvh9gAi1TuGngg6w0ag6rnPc4/OL2PpIEiN+0k+d3U7M7bjn/aFWYjtMAkWDUglaGkSSmpgoA/YihOucSz79561OOqb+6hD/7tbBmy5i1Z3rn+w9i57YXbdQchwAzq95SyenRVv", 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}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Fldvalue.2] = "" then [Fldvalue.1] else [Fldvalue.2]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Fldvalue.2"})
in
    #"Removed Columns"

[Jimmy801 · Accepted solution]

Hello @Mike_T 

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)=> 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)=> if row[Fldname]="C_subject" then "" else row[#"Fldvalue.1"] ,
        Replacer.ReplaceValue,
        { "Fldvalue.1"}
    )
in
    Replace2

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 too

Have fun

Jimmy