Remove specific words in rows with a conditional column

Question

I am having a problem creating a conditional column in Power bi that finds/looks up words that begin with specific letters and then remove it for the column as showing in this example below. Values to remove are words that begins with the letters; FCL,MON and WOD

Can anybody help me?

Thank you!

Answer 1

If they're all the s same length then you can write it more compactly like this:

if List.Contains({"WOD", "FCL", "MON"}, Text.Start([Input],3)) then "" else [Input]

Otherwise, you need to write each separately,

if Text.StartsWith([Input], "WOD") or
   Text.StartsWith([Input], "FCL") or
   Text.StartsWith([Input], "MON")
then "" else [Input]

Answer 2

You can use create a conditional column.

Here is a screenshot of the conditional column and the condition "begins with":

Here is the result:

Here is the M code, be carefull, when you are trying to create something with M you need to "repeat" him what you just did. For example I indicated that my file had headers in the step before the conditional column, and thus, M repeats this within the new step like this : #"Promoted Headers"

    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Added Conditional Column" = Table.AddColumn(#"Promoted Headers", "Custom", each if Text.StartsWith([Input], "FCL") then " " else if Text.StartsWith([Input], "MON") then " " else if Text.StartsWith([Input], "WOD") then " " else [Input])
in
    #"Added Conditional Column"

I hope this helps