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.
ok thanks that seem to work but i was checking this website and it shows someone got it to work. Is this not M power query?
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.
Thanks for this code :):
#"Replaced 68 to 680" = Table.ReplaceValue(#"Replaced H1353",each [Payroll Reference Number],each if [Reporting Unit] = "Store5" and [Payroll Reference Number] = "68" then "680" else [Payroll Reference Number],Replacer.ReplaceValue,{"Payroll Reference Number"}),I wanted to ammend an individual Payroll Number, based on the Store Name!
Experienced the issue whereby two people from different stored were given the same payroll number.
However, i did not want to create a new Payroll Number column, as most other people suggested.
So once again, thanks :)
ok it works but i got a minor issue after this step. All my column types has changed to any. I dono how this would affect it.
That seems to be a side-effect of Table.ReplaceValue.
You can either use my first solution or - after Table.ReplaceValue - select all columns and choose "Detect Data Type" on the Transform tab (check if the detected types are correct and adjust the generated code where applicable).
ok thanks, i might use the column solution for now.
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
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.
Great answer Anonymous .
Exactly what I needed to reference 2 columns!!
Paul
Any idea why this isn't working and when I add [Expiry_date] is works seemlesly
Hi Brinky,
I've had a play, and changing your formula to this should fix it:
= Table.ReplaceValue(#"Filtered Rows",
each [Route_Description],
each if [Route_No] = 125 then "Route" & Text.From([Route_No]) else [Route_Description],
Replacer.ReplaceText,{"Route_Description"})It seems that where you're combining the text "Route" with a field you need to explicitly tell it that you're combining text with text.
Cheers,
Matt
