Forum Discussion
Replacing values within column with if statement and text.startswith
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
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"})
6 Replies
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"})Hi if I wanted to do the same thing but in multiple column is it possible to do it in one step or should I duplicate the code for each column ? (thanks)
worked well, thank you Vijay_A_Verma
It is applicable for both.
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"})
