Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

How can I use a conditional statement in a custom function referencing a specific column?

Hello! I created a custom function to replace values based on a table merge and return the "original value" & "_NOT FOUND" when there is no match. I recorded the steps and then started replacing th...
Need Help
  • jbwtp's avatar
    jbwtp
    3 years ago

    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

jbwtp's avatar
jbwtp
Memorable Member
3 years ago

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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Thanks, jbwtp !

 

Inspired by your use of Record.Field() I tweaked the code a little and achieved my goal.

Instead of passing a list to the function I passed a 'text' containing the column name. This is beacuse my objective is to add "_NOT FOUND" to the column I'm passing:

let BulkReplace = 

(TAB_ORIGIN as table,
TAB_FROM_TO as table,
COL_LEFT_NAME as text,
COL_RIGHT_NAME as text,
COL_EXTRACTED as text) =>
let


    MERGE = Table.NestedJoin(TAB_ORIGIN , {COL_LEFT_NAME}, TAB_FROM_TO, {COL_RIGHT_NAME},"New"),
    EXPAND = Table.ExpandTableColumn(MERGE, "New", {COL_EXTRACTED}, {"TO"}),
    TO_2 = Table.AddColumn(EXPAND, "TO_2", each if [TO] = null then Record.Field(_, COL_LEFT_NAME)   & "_NOT FOUND" else [TO]),
    REMOVE = Table.RemoveColumns(TO_2,{ COL_LEFT_NAME , "TO"}),
    RENAME = Table.RenameColumns(REMOVE,{{"TO_2", COL_LEFT_NAME }})
in
    RENAME
in
    BulkReplace

 I had previosuly read the documentation of Record.Field() but couldn't get how to use till you gave the example.