Help with If Statement to modify text in query

[aallman]

I am brand new to M language and PowerQuery so I would greatly appreciate a little bit of an explanation along with an answer to this question so that I can learn!

I have a column of text values that are either "N/A" or a text string that starts with a space followed by various strings ex. " 5' Phos".

I need to alter this column to remove that first space from the string while leaving the "N/A" values the same.  I attempted to alter the function that auto-populated for me when I used the extract text after delimiter function (which worked on my strings but removed the "N/A" values). The function started as 

= Table.TransformColumns(PEN_OPS_CORE, {{"FivePrimeMod", each Text.AfterDelimiter(_, " "), type text}})

I tried to add an If statement but like I said I am brand new to this language and it did not work. 

= Table.TransformColumns(PEN_OPS_CORE_Fail_History, {{"FivePrimeMod", each if [FivePrimeMod]="N/A" then [FivePrimeMod] else Text.AfterDelimiter(_, " "), type text}})

any info and help would be greatly appreciated! including why my attempt did not work!

[amustafa · Accepted solution]

Hi @aallman 

You can simply right mouse click on the column, select Transform -> Trim to remove the leading and trailing spaces before you do other transformations. This will not remove your N/A values.