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.
Edit: wrong answer:
You can't replace values in a column based on values in another column.
Instead, create an additional column and replace the existing column with the new column.
Adjusted part of the code:
#"Replaced OTH" = Table.ReplaceValue(#"Promote Header"," ","OTH",Replacer.ReplaceValue,{"Gender"}),
#"Added Custom" = Table.AddColumn(#"Replaced OTH", "Custom", each if Text.Contains([Surname],"Manly") then "Male" else [Gender]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Gender"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Gender"}})
in
#"Renamed Columns"
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).
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.
