cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Barbs10
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...)

 

23 REPLIES 23
Priyaa_7
Helper I
Helper I

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.

shwetadalal
Resolver II
Resolver II

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
Not applicable

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

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
Not applicable

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
Not applicable

Hmm, that didn't work 😕 

Does an error message appear?

Anonymous
Not applicable

Yes, about a circular dependency error. 

 

CidcleyBarbosa
Advocate III
Advocate III

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

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

Hi @Rosepowerbi,
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

That worked 🙂 thank you Cidcley

This worked great, thank you @CidcleyBarbosa !

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.

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

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
Not applicable

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

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

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.

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"})

 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.