Replacing values within column with if statement and text.startswith

[MizzH_UK]

Hi 

Newbie here so please be gentle!

I'm using power query (M language). I am trying to modify a simple replace text command so that it is conditional using text.startswith. 

Ultimately I want to amend any items in a specified column which start with "X" so that the X is removed (replaced with a space).

e.g. 

X1001

X1002

XA200

becomes

1001

1002

A200

I do not want to add another conditional column.

My code (not working)  is as follows:

= Table.ReplaceValue(#"Replaced Value 2") each if Text.StartsWith([COLUMN],"X") then Replacer.ReplaceText([COLUMN],"X","") else [COLUMN])

Please can you help me get this working?

TIA 

Miriam

[Flippadood]

I have a similar problem set.  I am trying to use Text.StartsWith to replace names in the table of a .xml file.  In the column Organization.Subordinate, I want to replace all text that starts with 32539A with 32539AA.  When I right click on the cell and select replace value, I received the output below.  Being new to Power Query I don't understand the context of the answer provided in order to modify for my purposes.  Thanks.

= Table.ReplaceValue(#"Expanded Force_Organization","Text.StartWith 32539A","32539AA",Replacer.ReplaceText,{"Organization.Subordinate"})

[Anonymous]

Hi can this be used for Direct Query or only applicable for Import?

[Vijay_A_Verma]

It is applicable for both.

[MizzH_UK]

worked well, thank you @Vijay_A_Verma 

[Vijay_A_Verma · Accepted solution]

Use this (Replace Column1 and #"Added Custom" appropriately.

Table.ReplaceValue(#"Added Custom",each [Column1],each if Text.Start([Column1],1)="X" then Text.ReplaceRange([Column1],0,1,"") else [Column1], Replacer.ReplaceValue,{"Column1"})