Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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"})
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"})
Hi can this be used for Direct Query or only applicable for Import?
It is applicable for both.
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"})