Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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"
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"
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.