cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LSM Frequent Visitor
Frequent Visitor

Re: Query editor replacing values based on another column

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

LSM Frequent Visitor
Frequent Visitor

Re: Query editor replacing values based on another column

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

mattlancs Frequent Visitor
Frequent Visitor

Re: Query editor replacing values based on another column

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

Laz Member
Member

Re: Query editor replacing values based on another column

Thanks for this code Smiley Happy:

 

 #"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 Smiley Happy

 

cuiping Frequent Visitor
Frequent Visitor

Re: Query editor replacing values based on another column

sorry i am a beginner study from scratch, may i know what this for..."#"Replaced OTH""

Highlighted
mattlancs Frequent Visitor
Frequent Visitor

Re: Query editor replacing values based on another column

Hi Cuiping,

 

As far as I understand it, the bits before the = sign are just giving that line a name. So in this case it's saying "for my next trick I will perform an action called Replaced OTH". After the = sign, you get the name of the function that's being applied in this line, and then the first bit in the bracket is the name of the previous line. I presume this is so that it knows which order to run the lines in, somehow, but it can make it confusing to read at first.

 

You can change the names of the lines - Replaced OTH for example isn't a default sort of name, but it would have made the list of actions easier to read for its author - however if you're doing that in the editor you have to also remember to to change the name where it's mentioned in the next line, too.

 

Good luck!

 

Cheers,

Matt

PaulMac Regular Visitor
Regular Visitor

Re: Query editor replacing values based on another column

Great answer @Laz .

 

Exactly what I needed to reference 2 columns!!

 

Paul