Forum Discussion
Help with If Statement to modify text in query
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!
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.