Forum Discussion
How can I use a conditional statement in a custom function referencing a specific column?
Hi Anonymous,
Just replace COL_LEFT in TO_2 to Record.Field(_ , COL_LEFT{0}). You are passing the list(with column names) , the first item should be the name of the column that you want to add the "_not_found" to.
Cheers,
John
Hi Anonymous
What's the error message? I am not sure why you need to convert the join as a custom function, you can simply try to bring the value otherwise
try TAB_FROM_TO[To]{List.PositionOf(TAB_FROM_TO[From], [Original A])}
otherwise [Original A] & "_NOT FOUND"Hello Anonymous ,
Thank you very much for your help 🙂
What's the error message?
"We cannot apply the operator & to List and Text
Details:
Operator=&
Left=List
Right=_NOT FOUND"
"I am not sure why you need to convert the join as a custom function"
I have literally a dozen columns in the table in which I need to perform a 'bulk find and replace' using lookup tables. As the process of (1) merging, (2) expanding, (3) creating a custom column to return original value instead of null, (4) deleting the temporary columns and (5) renaming the custom column is going to be applied over and over, I thought it was wise to encapsulate it all in a function.So, I can keep the script on the main table clean and easy to explain to my boss.
"you can simply try to bring the value otherwise"
Yes, and your approach with 'try / otherwise' interested me, and it reduced what I first needed 5 steps to, (1) create a custom column, (2) delete the original, (3) rename the custom column.
Could I make it clear why a 'custom function' rather than just a 'custom column' was my choice?
Hi Anonymous
I mean your original solution was converting your steps to a custom function, and why it was not working when you try to replace hard coded column with COL_LEFT was because this COL_LEFT is a list when you defined.
Are you going to replace the values in 1 column at a time? If yes, you can convert this custom column approach to a custom function.
:
Are you going to replace the values in 1 column at a time? If yes, you can convert this custom column approach to a custom function.
Yes, Anonymous . I just don't know how to write such function 😞
Struggled here all day long trying to replace the 'hardcoded' names with variables but didn't succed.