Replace TEXT in a column using IF

[DanFromMontreal]

Is it possible the create a Customized Function that will NOT add a new column but rather do what it is suppose to do directly on the column selected.  Let me explain...

I want to be able to replace some entire cell value (not Text) by some others if some criteria are met.

But I would like to be able to reuse this to other function WITHOUT having to retype it.

I thought of a function that would look like this... 

If cell content = "A" then cell content = "A1"

If cell content = "B" then cell content = "B1"

If cell content = "C" then cell content = "C1"

else cell content = cell content (do not change it)

Thank you

[v-stephen-msft]

Hi @DanFromMontreal ,

You want a function in power query that can be called multiple times, right?

I think this similar post will inspire you, see the Message 6. You'll need a Transformation Table and a function to call.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BA_Pete · Accepted solution]

Hi @DanFromMontreal ,

You would use the standard Replace Values function from the transform tab on your column that you want values to be replaced in, but in the 'text to find' and 'replace with' boxes, you would just add random values, let's assume "aaa" and "bbb". This sets up the query step for you nicely.

You then edit the step something like this:

//Change your step that looks like this:
= Table.ReplaceValue(
    previousStep,
    "aaa",
    "bbb",
    Replacer.ReplaceText,{"columnName"}
)

//To something like this:
= Table.ReplaceValue(
    previousStep,
    each [columnName],
    each if [columnName] = "A" then [columnA]
    else if [columnName] = "B" then [columnB]
    else if [columnName] = "C" then [columnC]
    else [columnName],
    Replacer.ReplaceText,{"columnName"}
)

If this doesn't seem to do anything, then change the last 'Replacer.ReplaceText' to 'Replacer.ReplaceValue'.

Pete

[DanFromMontreal]

Great stuff @BA_Pete .  Worked like a charm.

If I want to reuse the same code for another column, I suspect that I have to copy this this and change the columnName.  

If I push the concept a little further, is it possible to create a Custom Function that will do the same thing but instead of replacing X number of time the ColumnName, I do it only once when I assign to which column my Function (fx) will perform the code? 

[DanFromMontreal]

Good morning @BA_Pete 

Based on your code, I tried to create a function that could be reused for multiple columns without having to rewrite the step.

My knowledge of the PowerM language is limited but I'm learning by grabbing code here and there and adjusting it to suit my needs.

I tried but failed to make it work.

I really need some held on this because our database is filled with terminology in French / English and having a function would ease our work to correct them.    

let
Source = (ValueToReplace as text) =>
let
L0=If ValueToReplace = "Existing building" then Replacer.ReplaceText(ValueToReplace, "Existing building", "Bâtiment existant")
If ValueToReplace = "New building" then Replacer.ReplaceText(ValueToReplace, "New building", "Nouveau bâtiment")
If ValueToReplace = "NA" then Replacer.ReplaceText(ValueToReplace, "NA", "Non applicable")
If ValueToReplace = "Yes" then Replacer.ReplaceText(ValueToReplace, "Yes", "Oui")
If ValueToReplace = "No" then Replacer.ReplaceText(ValueToReplace, "No", "Non")
else Replacer.ReplaceText(ValueToReplace, ValueToReplace, ValueToReplace)
in
L0
in
Source

Regards

[BA_Pete]

Hi @DanFromMontreal ,

I think you'd benefit from using a replacement table solution, something like this:

https://www.thebiccountant.com/2016/05/22/multiple-replacements-in-power-bi-and-power-query/ 

Scan over the blog post to understand the process that Imke is going for, then make sure to read the comment(s) from Bill Szysz at the end, where he really supercharges the solution.

Pete

[DanFromMontreal]

Thank @BA_Pete for the reading.

Went though it and to be quiet frank, I fell like a beginner sitting in an advance class.

Way over the basic knowledge I have of the PowerM language.

Someday I might be able to understand but from what I read, it is a funky solution, even for some more experienced programmer in the blog.

I would have thought a simpler solution would exist.