Forum Discussion
Helper IVReplace TEXT in a column using IF
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
Super UserHi 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
Helper IVGreat 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
SourceRegards
- 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.