cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Replace value column A if value in column B=criteria

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...)

 

5 REPLIES 5
Highlighted
Super User I
Super User I

Re: Replace value column A if value in column B=criteria

Hi @Barbs10

 

Please follow the link

https://community.powerbi.com/t5/Desktop/alter-values-in-one-column-based-on-a-value-in-another-colu...

 

specifically the content by LaurentCouartou.

 

After that Delete the orginal column A and rename the MyNewColumn as Column A

 

If this works please accept as solution and also give KUDOS to me and the linked article contributors.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Highlighted
Microsoft
Microsoft

Re: Replace value column A if value in column B=criteria

Hi @Barbs10,

 

Why don't you consider that solution using a conditional column? I think this is a very simple and valid solution in this scenario. For more advice, please make it more specific about your actual scenario.

 

Thanks,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Advocate I
Advocate I

Re: Replace value column A if value in column B=criteria

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 if [Job Classification] = "Intern" and [Indicator] = "Salary" then "Salary" else "!@#$%","Salary Intern",Replacer.ReplaceValue,{"Indicator"})

 

Cheers,
Cidcley

Highlighted
Regular Visitor

Re: Replace value column A if value in column B=criteria

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:

2020-06-09_15-09-57.png

 

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

Highlighted
Advocate I
Advocate I

Re: Replace value column A if value in column B=criteria

Hi @r0xxDaniel ,

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

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors