Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

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 IV
Advocate IV

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.