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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Anonymous 

 

I see, here is one way, though TAB_FROM_TO is not used, but easier to explain...

(TAB_ORIGIN as table,
TAB_FROM_TO as table,
COL_LEFT as text,
COL_RIGHT as list,
COL_EXTRACTED as list) =>
let
    #"Added Custom" = Table.AddColumn(TAB_ORIGIN, "Custom", each
        [original = Record.FieldValues(Record.SelectFields(_, {COL_LEFT})){0}?,
        a=try COL_EXTRACTED{List.PositionOf(COL_RIGHT,  original)} otherwise original&"_not found"][a]),
        
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{COL_LEFT}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", COL_LEFT}})
in
    #"Renamed Columns"

 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hello Anonymous,

It returned 'not found' for all records.

I guess it has to do with the fact that there is no reference to the lookup table, isn't it?

 

 

 

 

 

 

lookup table:

 

 

 

 

 

 

 

function call:

let
    Fonte = Excel.CurrentWorkbook(){[Name="BASE"]}[Content],
    #"Tipo Alterado" = Table.TransformColumnTypes(Fonte,{{"Original A", type text}, {"Original B", type text}}),
    BulkReplace = BulkReplace3 ( #"Tipo Alterado", DE_PARA, "Original A", {"De"}, {"Para"} )
in
    BulkReplace