This article will show you how to find and replace values in Power Query that start with a certain value of your choice.
Here is an example table with references that should all begin with ‘A0’. However, some values are missing the ‘A’.
Therefore we want to find and replace all values that begin with ‘0’ and add an ‘A’ in front. See the values highlighted in red below.
To find and replace these values starting with ‘0’ in this example, first click the ‘fx’ button to add a new step to your query, as shown below.
You will then need to write some M code to find and replace values starting with ‘0’.
Here is the M code that will find values where the first character is ‘0’ (using Text.Start), from the column (using Text.From).
In the IF statement, it will then replace any values starting with ‘0’ and add an ‘A’ to the start of it (using “A” and &).
= Table.TransformColumns(
#"Changed Type",
{{
"References",
each if Text.Start(Text.From(_),1)="0"
then
"A"&Text.From(_)
else _
}})The text in bold is what you will need to edit based on your query, i.e. the name of your previous step, column name, the value to find and replace, etc.
See below:
= Table.TransformColumns(
#"Previous Step",
{{
"Column Name",
each if Text.Start(Text.From(_),Number of Characters)="Value to Find"
then
"Value to Replace"&Text.From(_)
else _
}})After creating a new step and writing this function, these are the results from the example:
