M Language Conditional Statements - How To

[agd50]

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?

[BA_Pete · Accepted 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

[v-junyant-msft · Accepted solution]

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.

[BA_Pete · Accepted 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