Forum Discussion

aallman's avatar
aallman
Helper II
2 years ago
Solved

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.

     

1 Reply

  • amustafa's avatar
    amustafa
    Solution Sage

    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.