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 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.

Anonymous's avatar
Anonymous
Not applicable
3 years ago
:

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.

  • 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