POWER QUERY - Ability to add a prefix to data that does not have the prefix

[Datagulf]

I am trying to clean a column that has some inconsistent data.

Current Column		Desired Column
13B		Prefix 13B
Prefix 15C		Prefix 15C
Prefix 24D		Prefix 24D
Prefix 23A		Prefix 23A
15D		Prefix 15D

How do I add the suffix to the column without the Prefix word. 

[slorin · Accepted solution]

Hi

With Table.ReplaceValue

Table.ReplaceValue(
   YourSource,
   each [YourColumn],
   each if Text.StartsWith([YourColumn],"Prefix") then [YourColumn] else "Prefix " & [YourColumn],
   Replacer.ReplaceText,
   {"YourColumn"}
)

Stéphane 

[Manoj_Nair · Accepted solution]

@Datagulf- Try this steps, hope this should work.

Add a custom column:

• Select the column that you want to modify.
• Go to the "Add Column" tab in the Power Query Editor and click on the "Custom Column" button.
• In the "Custom Column" dialog box, enter a name for the new column (e.g., "Column Test").
• In the "Custom column formula" field, enter the following formula:

if Text.StartsWith([Current Column], "Prefix") then [Current Column] else "Prefix " & [Current Column]

Try following these steps, you should have a new column called "Column Test" that contains the data with the prefix added where necessary.

Let me know if this works for you. Thanks.

[Dhairya · Accepted solution]

Hey @Datagulf 
Please copy the following code in your advanced editor

let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"i45WMjR2UorViVYKKEpNy6xQMDR1RuYambigcI0dwVxDU6BwLAA=",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [#"Current Column" = _t]
),
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Current Column", type text}}),
#"Added Custom" = Table.AddColumn(
#"Changed Type",
"Desired Column",
each
if Text.StartsWith([Current Column], "Prefix") then
[Current Column]
else
"Prefix " & [Current Column]
)
in
#"Added Custom"

You will get the desired result as below

If it helps please mark my solution as accepted! 

[Manoj_Nair]

@Datagulf, Hi Try this solution. In this formula, Text.StartsWith checks if the text starts with "Prefix ", and if it does, it keeps the text as it is. If it doesn't, Text.Combine adds "Prefix " to the beginning of the text.

if Text.StartsWith([Current Column], "Prefix ") 
    then [Current Column] 
    else Text.Combine({"Prefix ", [Current Column]}, "")

If this post helps to find solution would be happy if you could mark my post as a solution and give it a thumbs up

Best regards

Manoj Nair
Linkedin - https://www.linkedin.com/in/manoj-nair-%E2%98%81-344666104/

[slorin · Accepted solution]

Hi

With Table.ReplaceValue

Table.ReplaceValue(
   YourSource,
   each [YourColumn],
   each if Text.StartsWith([YourColumn],"Prefix") then [YourColumn] else "Prefix " & [YourColumn],
   Replacer.ReplaceText,
   {"YourColumn"}
)

Stéphane 

[Datagulf]

Hello . Thanks for the reply. Please help with the steps rather than the code...

[Manoj_Nair · Accepted solution]

@Datagulf- Try this steps, hope this should work.

Add a custom column:

• Select the column that you want to modify.
• Go to the "Add Column" tab in the Power Query Editor and click on the "Custom Column" button.
• In the "Custom Column" dialog box, enter a name for the new column (e.g., "Column Test").
• In the "Custom column formula" field, enter the following formula:

if Text.StartsWith([Current Column], "Prefix") then [Current Column] else "Prefix " & [Current Column]

Try following these steps, you should have a new column called "Column Test" that contains the data with the prefix added where necessary.

Let me know if this works for you. Thanks.