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

[Anonymous]

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 the hardcoded names with variables, however, I'm struggling to replace [

[Original A], which is in step TO_2.

If I smply try to replace it with COL_LEFT, I get the error "Couldn't apply the operator & to List and Text"

Original A  Original B

1	aaaaaaaaa
2	bbbbbbbb
3	cccccccccc
4	dddddddd

From         To

1	9
2	8
3	7

let BulkReplace = 
(TAB_ORIGIN as table,
TAB_FROM_TO as table,
COL_LEFT as list,
COL_RIGHT as list,
COL_EXTRACTED as list ) =>
let
    MERGE = Table.NestedJoin(TAB_ORIGIN , COL_LEFT, TAB_FROM_TO, COL_RIGHT,"New"),
    EXPAND = Table.ExpandTableColumn(MERGE, "New", COL_EXTRACTED, {"TO"}),
    TO_2 = Table.AddColumn(EXPAND, "TO_2", each if [TO] = null then [Original A] & "_NOT FOUND" else [TO]),
    REMOVE = Table.RemoveColumns(TO_2,{"Original A", "TO"}),
    RENAME = Table.RenameColumns(REMOVE,{{"TO_2", "Original A"}})
in
    RENAME
in 
BulkReplace

Current result:

Original B        Original A

aaaaaaaaa	9
bbbbbbbb	8
cccccccccc	7
dddddddd	error

I expected the value "4_NOT FOUND" instead of error.

I understand that concatenation can only happen with equal data types, but doesn't the keyword each would be enough to loop through the items of the list and do the trick?

What am I missing here?

[jbwtp · Accepted solution]

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]

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.

[Vera_33]

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"

[Anonymous]

Hello @Vera_33 ,

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?

[Vera_33]

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]

:

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, @Vera_33 .  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.

[Vera_33]

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]

Hello @Vera_33,

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