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.
I'm looking to add a prefix to an existing column but only if it doesn't already have the prefix/conditional.
Any suggestions for how to do this?
Solved! Go to Solution.
Hi @agd50 ,
Difficult to give an exact answer without seeing an example of your data, but the basic conditional structure in Power Query M is as follows:
if <this thing is true>
then <do this thing>
else if <this other thing is true>
then <do this other thing>
else <do this escape action>
The "<do this thing>" part can be to run a function or to declare a variable etc. Therefore, a new custom QUERY STEP for your purpose might look something like this:
Table.ReplaceValue(
PreviousStepName,
each [ColumnToCheck],
each if Text.StartsWith([ColumnToCheck], "Prefix")
then [ColumnToCheck]
else "Prefix " & [ColumnToCheck],
Replacer.ReplaceText,
{"ColumnToCheck"}
)
Pete
Proud to be a Datanaut!
Hi @agd50 ,
I hope this will help you.
Suppose, I want to prefix all rows in column1 with "1. Here I chose to create a new column in order to allow for a clearer comparison.
Here is the content in the advanced editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtRzVIrViVZyApPOYNJQzwVMuyrFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "New", each if Text.Start([Column1], 2) = "1." then [Column1] else "1."&[Column1])
in
#"Added Custom"
The final output is like below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @agd50 ,
I hope this will help you.
Suppose, I want to prefix all rows in column1 with "1. Here I chose to create a new column in order to allow for a clearer comparison.
Here is the content in the advanced editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtRzVIrViVZyApPOYNJQzwVMuyrFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "New", each if Text.Start([Column1], 2) = "1." then [Column1] else "1."&[Column1])
in
#"Added Custom"
The final output is like below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @agd50 ,
Difficult to give an exact answer without seeing an example of your data, but the basic conditional structure in Power Query M is as follows:
if <this thing is true>
then <do this thing>
else if <this other thing is true>
then <do this other thing>
else <do this escape action>
The "<do this thing>" part can be to run a function or to declare a variable etc. Therefore, a new custom QUERY STEP for your purpose might look something like this:
Table.ReplaceValue(
PreviousStepName,
each [ColumnToCheck],
each if Text.StartsWith([ColumnToCheck], "Prefix")
then [ColumnToCheck]
else "Prefix " & [ColumnToCheck],
Replacer.ReplaceText,
{"ColumnToCheck"}
)
Pete
Proud to be a Datanaut!
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.