Forum Discussion
Query editor replacing values based on another column
You are right. I was confusing Table.ReplaceValues with Table.TransformColumns. :smileyembarrassed:
My solution works though, but the code you are looking for:
#"Replaced Value" = Table.ReplaceValue(#"Replaced OTH",each [Gender],each if [Surname] = "Manly" then "Male" else [Gender],Replacer.ReplaceValue,{"Gender"})Edit: it seems you switched "old" and "new" in your cide.
You are right. I was confusing Table.ReplaceValues with Table.TransformColumns. :smileyembarrassed:
My solution works though, but the code you are looking for:
#"Replaced Value" = Table.ReplaceValue(#"Replaced OTH",each [Gender],each if [Surname] = "Manly" then "Male" else [Gender],Replacer.ReplaceValue,{"Gender"})
Edit: it seems you switched "old" and "new" in your cide.
Is there a way to do the same for multiple columns at once?
I need to update entire row as #NA if a certain value is found in a column
I just got this working for multiple columns with the following line:
= Table.ReplaceValue(#"Expanded TS opps", null, each if [Sales Stage] = "Closed" then "Closed" else "Absent",Replacer.ReplaceValue,{"SP Status", "TS Status"})
This is part of a list of potential projects we might work on. This list is physically repeated in two other places, on our Sharepoint and in our timesheet system, and I wanted to check they line up to some degree. I've merged my queries, now I wanted to check where there's a null value in the Sharepoint or Timesheet system lists, if the project's closed on the master list, consider it closed, otherwise mark it as absent.
So what this is doing, blow by blow:
= Table.ReplaceValue( <- we're replace some values here
#"Expanded TS opps", <- this is just the name of the previous step. Yours will be different.
null, <- find null values to replace
each if [Sales Stage] = "Closed" then "Closed" else "Absent", <- check what the master list status is, and respond accordingly ...
Replacer.ReplaceValue,{"SP Status", "TS Status"}) <- ... in these two columns
I hope that's clear enough.
Hi mattlancs
I hope it's okay to ask another question to this old post. I tried to use your example to replace any value over multiple columns based on a criteria but can't seem to get it to work.
So - in your example. What if you wanted to replace not only null but any value from "SP Status" and "TS Status" based on your criteria.
Thanks in advance
Lars
Hi mattlancs
I hope it's okay to ask another question to this old post. I tried to use your example to replace any value over multiple columns based on a criteria but can't seem to get it to work.
So - in your example. What if you wanted to replace not only null but any value from "SP Status" and "TS Status" based on your criteria.
Thanks in advance
Lars
Hi Lars,
Sorry for the slow reply I only stumbled upon your question when I found this thread looking up the same problem again!
I've wrestled with this for a while but can't get it to work for more than one column. To do one column, replacing the null with each [#"TS opps.Status"] eventually worked. But I don't know how to refer to 'every value in either column', which seems like it should be straightforward. Then again I still don't understand what that little # adds to the previous example...
Apologies I can't give you more help - hopefully you're well past the problem by now anyway.
Matt
mattlancs, how to skip specifying an else?
I.e. i want to replace the value of a column if another column = something. Else leave everything as is.
Hi gk2go,
If you want it to stay the same as before, you can replace it with itself, so you put the same column name in as the one you're using the replace function on.
For example, in my table of employees, this line goes through the ContractedHours and looks for people with a JobTitle of "Admin". Then, if they're full time (i.e. have ContractedHours = 40) then replace that 40 with 100, otherwise leave it as it is.
= Table.ReplaceValue(#"Capitalized Each Word", 40, each if [JobTitle] = "Admin" then 100 else [ContractedHours], Replacer.ReplaceValue,{"ContractedHours"})
Step by step:
= Table.ReplaceValue( <- summoning the replace-some-values function
#"Capitalized Each Word", <- the name of the previous step, yours will be different
40, <- the number we're going to replace
each if [JobTitle] = "Admin" then 100 else [ContractedHours], <- here's the key bit: after the "else", replace it with the ContractedHours i.e. replace it with itself i.e. don't change anything
Replacer.ReplaceValue,{"ContractedHours"}) <- which column we're doing the replacement in.
Hope that's clear.
Cheers,
Matt
The last argument {"Gender"} is the list of columns in which values must be replaced. If you first select the applicable columns, and then choose for Replace Values, the generated code will include the names of the selected columns in the last argument.
