Replace value column A if value in column B=criteria

[Barbs10]

Hi!

I want to replace the value in one column if the value in a second column equals a criteria.

For example

Column A       Column B

A                     1

B                     2

I want to use the advanced editor so that if Column B=2, then Column A=C to get the result:

Column A       Column B

A                     1

C                     2

I have been trying to use this:

#"Replaced Value" =  if (each[Column B]=2) then Table.ReplaceValue(#"Table name", "B", "C",Text.Replace, {"Column A"} ) else null

But it is not working..Any ideas that don't require to use a conditional column (I have to do this replacements on a lot of columns...)

[Anonymous]

Hi I've tried conditional replacing method as suggested but I hit an error stating this isnt supported for Direct Query. Can anyone help on this? Thank you.

[Anonymous]

Hey @Barbs10 

Why don't you create a new custom column with the code:

if column B = 2 then C else Column A.

Later you can delete the old column.

[Anonymous]

Hi, 

I'm trying this code: 

= Table.ReplaceValue(#"Ark1",each [Column1], each if [Column2] = "Forhold (uønsket)" and [Column1] = "Uønsket HMS-hendelse" then "Forhold" else [Column1],Replacer.ReplaceValue,{"NA"}) 

But I'm getting a circular dependency error. 
Any advice? 

Thanks

[CidcleyBarbosa]

Hi @Anonymous the error is at the end {"NA"} should be replaced by the column indicated in the second argument "Column1".

Here is the corrected code:

= Table.ReplaceValue(#"Ark1", each [Column1], each if [Column2] = "Forhold (uønsket)" and [Column1] = "Uønsket HMS-hendelse" then "Forhold" else [Column1],Replacer.ReplaceValue,{"Column1"}) 

Cheers,
Cidcley

[Anonymous]

I changed it to the correct headdings: 

= Table.ReplaceValue(#"Ark1", each [Type], each if [Forhold] = "Forhold (uønsket)" and [Type] = "Uønsket HMS-hendelse" then "Forhold" else [Type],Replacer.ReplaceValue,{"Type"}) 

Any more advice? 🙂 

[Anonymous]

Hmm, that didn't work 😕 

[CidcleyBarbosa]

Does an error message appear?

[Anonymous]

Yes, about a circular dependency error. 

[CidcleyBarbosa]

Hello everyone,

@Barbs10  you asked this question a long time ago ... but it is possible to do what you want (replace + if without adding / deleting columns).

Pay attention to my case. I have a database where there are different types of indicators (column [Indicator]) and I need to change an indicator from 'Salary' to 'Salary Intern' only when [Job classification] is 'Intern'.

This first code is a simple replacement. In this case, the replacement will occur on all occurrences, but to replace only when [Job Classification] is 'Intern', it will be necessary to add conditions, as you can see in the second code:

= Table.ReplaceValue(#"Filtered Rows","Salary","Salary Intern",Replacer.ReplaceValue,{"Indicator"})

= Table.ReplaceValue(#"Filtered Rows",each [Indicator], each if [Job Classification] = "Intern" and [Indicator] = "Salary" then "Salary Intern" else [Indicator],Replacer.ReplaceValue,{"Indicator"})

Cheers,
Cidcley

[Anonymous]

Hi Cidcley,

I've got a pretty much identical situation to you. I have [Reporting Unit] which contains values PF, ME and others. I want to change ME to PF where [Cat1] = "A2,A3,A4,A6". I tried adding a custom column called Reporting Unit2 and then writing the code you provided. It's coming up with an error message: The column 'Reporting Unit' of the table wasn't found.

= Table.AddColumn(#"Expanded Reporting Entity Mapping", "Reporting Unit2", each Table.ReplaceValue(#"Filtered Rows",each [Reporting Unit], each if [Reporting Unit] = "ME" and [Cat1] = "A2,A3,A4,A6" then "PF" else [Reporting Unit],Replacer.ReplaceValue,{"Reporting Unit"}))

Sorry if this is a stupid q, I'm new to PowerBI!

Thanks

Rose

[CidcleyBarbosa]

Hi @Anonymous,
Adding a new column, try this code:

= Table.AddColumn(#"Expanded Reporting Entity Mapping", "Reporting Unit2", each if [Reporting Unit] = "ME" and List.Contains({"A2","A3","A4","A6"}, [Cat1]) then "PF" else [Reporting Unit], type text)

Or, replancing in 'Reporting Unit' column, try this:

= Table.ReplaceValue(#"Expanded Reporting Entity Mapping", each [Reporting Unit], each if [Reporting Unit] = "ME" and List.Contains({"A2","A3","A4","A6"}, [Cat1]) then "PF" else [Reporting Unit],Replacer.ReplaceValue,{"Reporting Unit"}))

Cheers,
Cidcley

[Anonymous]

That worked 🙂 thank you Cidcley

[mansivora]

This worked great, thank you @CidcleyBarbosa !

[Iamwedgie]

Hi Cidcley,

I liked your example & I'm trying to apply a pretty similar change in my analysis of SharePoint sites, users and permissions. Whilst I'm not getting any errors the values in the column are not getting replaced. I'm trying to update the LoginType value to be "guest membership" rather than "membership" if the loginid contains "#ext#@mydomain.com", otherwise the value should not change. Example of the query being used is below.

#"Replaced Value6" = Table.ReplaceValue(#"Renamed Columns1", each [LoginType], each if Text.Contains([LoginId],"#ext#@mydomain.com") and [LoginType = "membership"] then "guest membership" else [LoginType],Replacer.ReplaceValue,{"LoginType"}),

Any thoughts on what I've done wrong? I'm guessing it'll be a trivial mistake that I just can't see.

[CidcleyBarbosa]

Hi @Iamwedgie your code has a error:
[LoginType = "membership"]
tries:
[LoginType] = "membership"

[Iamwedgie]

Thanks, I knew it was going to be a silly mistake 🙄

It still didn't seem to be doing what I expected, but I removed the next step in the transform, a Sort, and it all dropped into place. Put the sort back and its fine.

[Anonymous]

Hi Cidcley,

I'm trying to use something similar to your code. I need to change a value from "undone" to "done" if the condition hits. I would like to change only the value in the InstallStatus Column. It have to be set to "Client Count - Done" if the new antivirus program is installed and also if the old antivirus software is still there. The result should be "Client Count - Done". All clients with the older antivirus software (AntivirusProgram2) should get the installStatus "Client Count - Undone".  All other values will be set to null.

Data sample:

There are some special clients that have the old and the new antivirus software installed and they are done. But these clients will be also counted for undone.

What I tried is to use AddColumn and replaceValue to add a additional condition but this is not working. Do you have an idea how I could solve this problem?

AddColumn:

= Table.AddColumn(#"New Column", "InstallStatus", each if [SUITENAME] = "AntivirusProgram1" then "Client Count - Done" else if [SUITENAME] = "AntivirusProgram2" then "Client Count - Undone" else if [SUITENAME] = "AntivirusProgram1" and [SUITENAME] = "AntivirusProgram2" then "Client Count - Done" else null)

ReplaceValue:

= Table.ReplaceValue(#"New Column", each if [InstallStatus] = "Client Count - Done" and [SUITENAME] = "AntivirusProgram1" and [InstallStatus] = "Client Count - Undone" and [SUITENAME] = "AntivirusProgram2" then "Client Count - Undone","Client Count - Done",Replacer.ReplaceText,{"InstallStatus"})

Regards,

r0xx

[CidcleyBarbosa]

Hi @Anonymous ,

Try this:

= Table.ReplaceValue(#"New Column", each [InstallStatus], each if [InstallStatus] = "Client Count - Done" and [SUITENAME] = "AntivirusProgram1" and [InstallStatus] = "Client Count - Undone" and [SUITENAME] = "AntivirusProgram2" then "Client Count - Undone" else "Client Count - Done",Replacer.ReplaceValue,{"InstallStatus"})

Let me know if it works.

Cheers
Cidcley

[asah]

The issue I am facing is with the else parameter and what if I do not want to specify for else parameter, indicating that no changes required if the else parameter is evaluated.

[CidcleyBarbosa]

Hi @asah, change the argument after else to the name of the column where you want to replace, as in my last example:

= Table.ReplaceValue(#"New Column", each [InstallStatus], each if [InstallStatus] = "Client Count - Done" and [SUITENAME] = "AntivirusProgram1" and [InstallStatus] = "Client Count - Undone" and [SUITENAME] = "AntivirusProgram2" then "Client Count - Undone" else [InstallStatus],Replacer.ReplaceValue,{"InstallStatus"})