The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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"})
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
34 | |
19 | |
18 | |
16 | |
13 |